Forum Discussion
Merge two rows, but not every record has two rows
Hi, I'm having some issues merging rows that are coming into Power BI like this:
For example, rows 64/65, 66/67, 68/69 should be merged where Type is now on the same line as the other data. However, not ever record will have a subsequent row to merge, such as 62 though 64 above. Any help would be appreciated.
- Add a "Shifted Type" column whereby
- if the next row of the Date column = null then
- copy the next row of the Type column else
- retain the original Type column
- if the next row of the Date column = null then
- Filter out the rows where Date = null
- Remove the original Type column
- Rename the Shifted Type column = Type
The code below only shows a few of your columns, but can be easily extended.
let Source = Table.FromColumns( { List.Numbers(62,8,1), {"abc","def","ghi","","mno","","stu",""}, {"3/31/2022","4/1/2022","1/4/2022",null,"2/16/2022",null,"4/8/2022",null}, {"","","","PT-US","","PTO-US","","PTO-US"}}, type table[Row=Int64.Type, Name=text, Capacity Date=date,Type=text] ), //add a column such that if Capacity Date = null then move Type up one // Change "Source" in the below code to whatever your previous step really is shiftType = Table.FromColumns( Table.ToColumns(Source) & {List.Generate( ()=>[st=if Source[Capacity Date]{1} = null then Source[Type]{1} else Source[Type]{0}, idx=0], each [idx]<Table.RowCount(Source)-1, each [st=if Source[Capacity Date]{[idx]+2} = null then Source[Type]{[idx]+2} else Source[Type]{[idx]+1}, idx=[idx]+1], each [st] )}, type table[Row=Int64.Type, Name=text, Capacity Date=date,Type=text,Shifted Type = text] ), //Filter out the rows where there are nulls in the Date Column #"Filtered Rows" = Table.SelectRows(shiftType, each ([Capacity Date] <> null)), //delete the original Type column // then rename the Shifted Type => Type column #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Type"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Shifted Type", "Type"}}) in #"Renamed Columns"Before
After
- Add a "Shifted Type" column whereby
2 Replies
- ronrsnfldSuper User
- Add a "Shifted Type" column whereby
- if the next row of the Date column = null then
- copy the next row of the Type column else
- retain the original Type column
- if the next row of the Date column = null then
- Filter out the rows where Date = null
- Remove the original Type column
- Rename the Shifted Type column = Type
The code below only shows a few of your columns, but can be easily extended.
let Source = Table.FromColumns( { List.Numbers(62,8,1), {"abc","def","ghi","","mno","","stu",""}, {"3/31/2022","4/1/2022","1/4/2022",null,"2/16/2022",null,"4/8/2022",null}, {"","","","PT-US","","PTO-US","","PTO-US"}}, type table[Row=Int64.Type, Name=text, Capacity Date=date,Type=text] ), //add a column such that if Capacity Date = null then move Type up one // Change "Source" in the below code to whatever your previous step really is shiftType = Table.FromColumns( Table.ToColumns(Source) & {List.Generate( ()=>[st=if Source[Capacity Date]{1} = null then Source[Type]{1} else Source[Type]{0}, idx=0], each [idx]<Table.RowCount(Source)-1, each [st=if Source[Capacity Date]{[idx]+2} = null then Source[Type]{[idx]+2} else Source[Type]{[idx]+1}, idx=[idx]+1], each [st] )}, type table[Row=Int64.Type, Name=text, Capacity Date=date,Type=text,Shifted Type = text] ), //Filter out the rows where there are nulls in the Date Column #"Filtered Rows" = Table.SelectRows(shiftType, each ([Capacity Date] <> null)), //delete the original Type column // then rename the Shifted Type => Type column #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Type"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Shifted Type", "Type"}}) in #"Renamed Columns"Before
After
- Add a "Shifted Type" column whereby
- HotChilliCommunity Champion
What would the Type be for the first 2 rows (blank or PTO-US). If it is PTO-US, you might get away with a Fill Up on that column (after replacing the contents with null) OR a Fill Down on all the other fields (same advice for replacing blanks with null)
If it's not that straightforward, you might need to add an Index column then write a custom column to check for null in Capacity Date and then assign Type from the current index to row index - 1