powerquery - insert a null vallue in a column using power query - Stack Overflow

admin管理员组

文章数量:1122846

I have a table with 7 columns: Date, Time, Open, High, Low, Close, TickVol. In excel, to compute for the %volume change, on cell H3=if iserror((G3/G2-1)*100,"",(G3/G2-1)*100). I would just copy and paste that formula from H4 onwards and it works excel. But when i try to do it in Power Query, it still works but there is this annoying issue that the results start on the first row when it should start on the second row. Basically, it doesn't align well. I tried to workaround it by trying to insert a null value on the first row but it doesn't work. tried several methods but none would work. this is the last code that i ended up with but it won't work. I know i could easily just shift the data by a simple copy and paste once it loads in excel but I just find it annoying that I couldn't figure out how to fix this simple issue using code.

this works:

let
    Source = Excel.CurrentWorkbook(){[Name="tbl_BTCUSD_M30"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 0, 1, Int64.Type),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index"}, #"Added Index1", {"Index.1"}, "Added Index1", JoinKind.LeftOuter),
    #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"<TICKVOL>"}, {"Added Index1.<TICKVOL>"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Added Index1", "%VOLUME CHANGE", each [#"Added Index1.<TICKVOL>"]/[#"<TICKVOL>"]-1),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index", "Index.1", "Added Index1.<TICKVOL>"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"%VOLUME CHANGE", Percentage.Type}})
    
in
    #"Changed Type"

this doesn't:

let
    Source = Excel.CurrentWorkbook(){[Name="tbl_BTCUSD_M30"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 0, 1, Int64.Type),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index"}, #"Added Index1", {"Index.1"}, "Added Index1", JoinKind.LeftOuter),
    #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"<TICKVOL>"}, {"Added Index1.<TICKVOL>"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Added Index1", "%VOLUME CHANGE", each [#"Added Index1.<TICKVOL>"]/[#"<TICKVOL>"]-1),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index", "Index.1", "Added Index1.<TICKVOL>"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"%VOLUME CHANGE", Percentage.Type}}),
    InsertNull = Table.InsertRows(Source, 0, {["%VOLUME CHANGE" = null]}) 
in 
InsertNull

I have a table with 7 columns: Date, Time, Open, High, Low, Close, TickVol. In excel, to compute for the %volume change, on cell H3=if iserror((G3/G2-1)*100,"",(G3/G2-1)*100). I would just copy and paste that formula from H4 onwards and it works excel. But when i try to do it in Power Query, it still works but there is this annoying issue that the results start on the first row when it should start on the second row. Basically, it doesn't align well. I tried to workaround it by trying to insert a null value on the first row but it doesn't work. tried several methods but none would work. this is the last code that i ended up with but it won't work. I know i could easily just shift the data by a simple copy and paste once it loads in excel but I just find it annoying that I couldn't figure out how to fix this simple issue using code.

this works:

let
    Source = Excel.CurrentWorkbook(){[Name="tbl_BTCUSD_M30"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 0, 1, Int64.Type),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index"}, #"Added Index1", {"Index.1"}, "Added Index1", JoinKind.LeftOuter),
    #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"<TICKVOL>"}, {"Added Index1.<TICKVOL>"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Added Index1", "%VOLUME CHANGE", each [#"Added Index1.<TICKVOL>"]/[#"<TICKVOL>"]-1),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index", "Index.1", "Added Index1.<TICKVOL>"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"%VOLUME CHANGE", Percentage.Type}})
    
in
    #"Changed Type"

this doesn't:

let
    Source = Excel.CurrentWorkbook(){[Name="tbl_BTCUSD_M30"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 0, 1, Int64.Type),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index"}, #"Added Index1", {"Index.1"}, "Added Index1", JoinKind.LeftOuter),
    #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"<TICKVOL>"}, {"Added Index1.<TICKVOL>"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Added Index1", "%VOLUME CHANGE", each [#"Added Index1.<TICKVOL>"]/[#"<TICKVOL>"]-1),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index", "Index.1", "Added Index1.<TICKVOL>"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"%VOLUME CHANGE", Percentage.Type}}),
    InsertNull = Table.InsertRows(Source, 0, {["%VOLUME CHANGE" = null]}) 
in 
InsertNull

• powerquery
• m

Share Improve this question Follow edited Nov 22, 2024 at 2:00 Michal 5,28144 gold badges2626 silver badges4242 bronze badges asked Nov 22, 2024 at 0:56 KoachRKoachR 922 bronze badges 2

• 1 Please check out the markdown formatting guide and the minimal reproducible example guide. Without proper formatting, this is hard to parse, and without a proper MRE, it's hard to answer. – Anerdw Commented Nov 22, 2024 at 0:59
• You can use the same technique shown you in your previous question, to which you did not bother to select a valid response, nor indicate the problem with the answers provided. – Ron Rosenfeld Commented Nov 26, 2024 at 12:45

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

You seem to be overcomplicating a fairly simple issue. What you need is a way to get the previous value of TicVol except for the first one where it should be null. You can do it using an index column:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type1" = Table.TransformColumnTypes(Source,{{"Open", type number}, {"High", type number}, {"Low", type number}, {"Close", type number}, {"TickVol", type number}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 0, 1, Int64.Type),
    // Add previous value of TickVol except for the first one where null
     #"Shifted TickVol" = Table.AddColumn(#"Added Index", "Previous TickVol", each try Source[TickVol]{[Index] - 1} otherwise null),
    // Calculate the %VOLUME CHANGE
    #"Added Volume Change" = Table.AddColumn(#"Shifted TickVol", "%VOLUME CHANGE", each if [Previous TickVol] = null then null else ([TickVol] / [Previous TickVol] - 1)),
    // Remove unnecessary columns
    #"Removed Columns" = Table.RemoveColumns(#"Added Volume Change",{"Previous TickVol", "Index"}),
    // Change type to Percentage
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns", {{"%VOLUME CHANGE", Percentage.Type}})
in
    #"Changed Type"

本文标签： powerqueryinsert a null vallue in a column using power queryStack Overflow