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 (ItemsTable2).

 

ItemsTable                         ItemsTable2

DateItem 1/15/20241/20/20241/25/2024
1/15/2024Item 1 Item 1Item 1Item 1
1/15/2024Item 2 Item 2Item 2Item 2
1/15/2024Item 3 Item 3Item 3Item 3
1/15/2024Item 4 Item 4Item 4Item 4
1/20/2024Item 1    
1/20/2024Item 2    
1/20/2024Item 3    
1/20/2024Item 4    
1/25/2024Item 1    
1/25/2024Item 2    
1/25/2024Item 3    

 

I tried a Matrix, but this only lists either the first or last value under the Item column. Is there a way to list all the Item values?

Any suggestions will be much appreciated.

 

1/15/20241/20/20241/25/2024
Item 1Item 1Item 1
Need Help

9 Replies

  • gmsamborn's avatar
    gmsamborn
    Super User
    2 years ago

    Hi arthur_mq 

     

    You could use a matrix with [Date] on columns and the following measure in values.

     

    Vertical List = 
    CONCATENATEX(
        'Table',
        'Table'[Item],
        UNICHAR( 10)
    )

     

     

    Vertical List.pbix

     

    Let me know if this was what you wanted.

     

    • arthur_mq's avatar
      arthur_mq
      Frequent Visitor
      2 years ago

      gmsamborn  I think this could be the solution. Is there a way to number each item and sort them in ascending order and also two spaces between each item? 

      • gmsamborn's avatar
        gmsamborn
        Super User
        2 years ago

        Hi arthur_mq 

         

        I'm not sure how to "number each item" but sorting is handled with CONCATENATEX's optional parameters.  Can you explain this numbering?

         

        (I would probably wait to see what Ashish_Mathur  can come up with using a Power Query approach since this CONCATENATEX() usage is very limited and mainly for display purposes.)

         

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