Forum Discussion

jheath's avatar
jheath
Frequent Visitor
4 years ago
Solved

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
    • 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

     

     

2 Replies

    • 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
    • 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

     

     

  • HotChilli's avatar
    HotChilli
    Community 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