Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
arthur_mq
Frequent Visitor

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
9 REPLIES 9
Ashish_Mathur
Super User
Super User

Hi,

Would you be OK with a Power Query solution?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

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.

Ashish_Mathur_0-1707694840502.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
gmsamborn
Super User
Super User

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.

 

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

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

 

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

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. 

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.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.