如何将值复制到 PowerBI 中特定列的下一行？

在 M / Powerquery 中做的一种方法

使用用户界面，方法 1：

添加列..索引列...

添加列 .. 自定义列 ... 列名称自定义和公式

=try #"Added Index"{[Index]-1}[Column1] otherwise null

这将从 Column1 中的前一行中获取值

添加列 .. 自定义列 ... 列名NewColumn1和公式

= if [Column1] = null and [Custom]<>null then [Column2] else [Column1]

右键单击并删除 Column1、Index 和 Custom 列

单击 NewColumn1 并按 F2 并将列重命名为 Column1

完毕

手动编码方法二

创建一个将 Column1 向下偏移 1 的新列。当第 1 列为空且新列有数字时，复制 Column2 中的值

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
// offset Column1 down by a row into new column
    shiftedList = {null} & List.RemoveLastN(Source[Column1],1),
    custom1 = Table.ToColumns(Source) & {shiftedList},
    custom2 = Table.FromColumns(custom1,Table.ColumnNames(Source) & {"Previous Row"}),
#"Added Custom" = Table.AddColumn(custom2, "Custom", each if [Column1]=null and [Previous Row]<>null then [Column2] else [Column1]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1", "Previous Row"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Column1"}})
in  #"Renamed Columns"

手动编码方法3

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
// offset Column1 down by a row into new column
    shiftedList = {null} & List.RemoveLastN(Source[Column1],1),
    custom1 = Table.ToColumns(Source) & {shiftedList},
    custom2 = Table.FromColumns(custom1,Table.ColumnNames(Source) & {"Previous Row"}),
 #"Replaced Value" = Table.ReplaceValue(custom2,  each [Column1],  each if [Previous Row]<>null and [Column1]=null  then [Column2] else [Column1] ,Replacer.ReplaceValue,{"Column1"}),
#"Removed Columns" = Table.RemoveColumns(#"Replaced Value",{"Previous Row"})  
in #"Removed Columns"