Forum Discussion

probey's avatar
probey
New Member
2 years ago
Solved

Turn two header rows into columns

I have a file where each group of entries on a given date is separated by two headers: one with the date and another with the location (in italics and underline below):

Obviously, I want to pull the date and location data out as additional columns and fill each row of those new columns with the corresponding data. There is no set number of entries between each grouping.

Need Help
  • spinfuzer's avatar
    spinfuzer
    2 years ago

     

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Account", type any}, {"Amount", type number}, {"Customer", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Account"}, 
        {{"Rows",
            each 
                let 
                    ct = Table.RowCount(_)
                in
                    Table.FromColumns( 
                        Table.ToColumns(Table.Skip(_,2))
                        & {List.Repeat({_[Account]{0}},ct-2)} 
                        & {List.Repeat({_[Account]{1}},ct-2)},
                        Table.ColumnNames(_) & {"Date","Location"}
                    )
        }}, 
        GroupKind.Local, 
        (x,y) => Byte.From(try Date.From(y[Account]) is date otherwise 0)
    )
in
    #"Grouped Rows"

     

     

1 Reply

  • spinfuzer's avatar
    spinfuzer
    Solution Sage
    2 years ago

     

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Account", type any}, {"Amount", type number}, {"Customer", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Account"}, 
        {{"Rows",
            each 
                let 
                    ct = Table.RowCount(_)
                in
                    Table.FromColumns( 
                        Table.ToColumns(Table.Skip(_,2))
                        & {List.Repeat({_[Account]{0}},ct-2)} 
                        & {List.Repeat({_[Account]{1}},ct-2)},
                        Table.ColumnNames(_) & {"Date","Location"}
                    )
        }}, 
        GroupKind.Local, 
        (x,y) => Byte.From(try Date.From(y[Account]) is date otherwise 0)
    )
in
    #"Grouped Rows"