Rearrange table by grouping values in columns

[arthur_mq]

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

Date	Item		1/15/2024	1/20/2024	1/25/2024
1/15/2024	Item 1		Item 1	Item 1	Item 1
1/15/2024	Item 2		Item 2	Item 2	Item 2
1/15/2024	Item 3		Item 3	Item 3	Item 3
1/15/2024	Item 4		Item 4	Item 4	Item 4
1/20/2024	Item 1
1/20/2024	Item 2
1/20/2024	Item 3
1/20/2024	Item 4
1/25/2024	Item 1
1/25/2024	Item 2
1/25/2024	Item 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/2024	1/20/2024	1/25/2024
Item 1	Item 1	Item 1

[Ashish_Mathur]

Hi,

Would you be OK with a Power Query solution?

[arthur_mq]

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

[Ashish_Mathur]

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.

[gmsamborn]

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]

@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]

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

[arthur_mq]

Hi @gmsamborn 

I modified the DAX formula to add a number to each item and sort them, but the sorting is not working. Any ideas?

Vertical List_M =
CONCATENATEX(
'Table',
" (" & FORMAT(RANKX('Table', 'Table'[Item],,ASC), "0") & ") "& 'Table'[Item] & UNICHAR(10) & UNICHAR(10)
)

[gmsamborn]

Hi @arthur_mq 

Would something like this help?

Vertical List_M =
CONCATENATEX(
    'Table',
    " ("
        & FORMAT(
            RANKX(
                'Table',
                'Table'[Item],
                ,
                ASC
            ),
            "0"
        ) & ") " & 'Table'[Item],
	UNICHAR( 10 ),		-- Separator (otpional)
	,					-- Sorting expression (optional)
	ASC					-- Sorting direction (optional)
)

I'm not sure that will work and am pretty much out of ideas.   Like I mentioned earlier, this method has limited usefulness. 

[arthur_mq]

Hi @gmsamborn 

The items are long text sentences, and I need to asign a number to each item and sort them like this:

1) Establish Appropriations, Advertise and Award Construction Contract for Pedestrian Safety           Improvements at Four Intersections in the Communities.

2) Ordinance Amendment to the Grading, Clearing and Watercourses Ordinance to Allow By-          Right Ministerial Grading Permits for By-Right Housing Projects

3) Establish Appropriations, Advertise and Award a Construction Contract for Pedestrian                  Crossing Enhancements at 17 Locations.