Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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 |
Hi,
Would you be OK with a Power Query solution?
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.
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)
)
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
72 | |
62 | |
51 | |
48 |
User | Count |
---|---|
204 | |
86 | |
62 | |
59 | |
56 |