How do you duplicate a value to the next row in a specific column in PowerBI?

user_9959526 0
Viewed 406

So I have this PBI report, and I have this base column here:

column 1

I decided to split this column into two using a custom column with this code: (Column 3 is just an int column)

= Table.AddColumn(#"Added Conditional Column", "Column1", each if [#"Column3"] > 0 then [Base Column] else null)
= Table.AddColumn(#"Added Custom", "Column2", each if [#"Column3"] <= 0 then [Base Column] else null)

The resulting Column 1 and 2 looked like this:

column 1 and 2

Everything looked fine until I plotted them in a line chart, which left a gap: enter image description here

I realized I had to insert the 279.8258333 value on Column 1 as well to make it look connected.

I've tried different Table.InsertRow but I can't seem to find anything on just a specific cell in a table ( the adjacent cell of the first value in Column 2). Basically I need the Column1 and 2 to have the 279 value.

Any help would be appreciated!

powerbi-desktop powerbi powerquery
1 Answers

One way to do in M / Powerquery

Using user interface, method 1:

Add column .. index column ...

Add column .. custom column ... with column name Custom and formula

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

That will grab the value from the prior row in Column1

Add column .. custom column ... with column name NewColumn1 and formula

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

Right click and remove Column1, Index and Custom columns

Click NewColumn1 and hit F2 and rename column to be Column1

done

Manual code method 2

(The three rows to create an offset column work faster on larger data sets then using an index to refer to prior row)

Create a new column that offsets Column1 down by one. When column 1 is a null and the new column has a number, copy the value from 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"

Manual code method 3

(The three rows to create an offset column work faster on larger data sets then using an index to refer to prior row)

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"

enter image description here

user_92642300
Related