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 pu...
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"

     

     

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"