Forum Discussion

arthur_mq's avatar
arthur_mq
Frequent Visitor
2 years ago

Rearrange table by grouping values in columns

Hi, I have a table that list items by date (ItemsTable below). Each date lists more than one item. I want to rearrange this table by grouping the dates in columns with their items listed below (Ite...
Need Help
Ashish_Mathur's avatar
Ashish_Mathur
Super User
2 years ago

Hi,

Would you be OK with a Power Query solution?

arthur_mq's avatar
arthur_mq
Frequent Visitor
2 years ago

Hi Ashish_Mathur  yes, a Power Query solution would be great!

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    Hi,

    This M code works

    let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Item", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Date"}, {{"Count", each Table.AddIndexColumn(_,"Index",1,1), type table [Date=nullable date, Item=nullable text, Index=nullable number]}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Item", "Index"}, {"Item", "Index"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Count", {{"Date", type text}}, "en-IN"), List.Distinct(Table.TransformColumnTypes(#"Expanded Count", {{"Date", type text}}, "en-IN")[Date]), "Date", "Item"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns"

    Hope this helps.