Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
If I have the following in a table:
date
01/09/21
01/09/21
01/10/21
01/10/21
01/10/21
01/11/21
01/11/21
01/11/21
01/11/21
01/12/21
01/12/21
01/12/21
01/01/22
01/01/22
and I need to get to the following:
date index
01/09/21 1
01/09/21 1
01/10/21 2
01/10/21 2
01/10/21 2
01/11/21 3
01/11/21 3
01/11/21 3
01/11/21 3
01/12/21 4
01/12/21 4
01/12/21 4
01/01/22 5
01/01/22 5
can I do it in Power Query?
The number of rows for each date is not fixed, and I have other columns in my table which I need to preserve.
Solved! Go to Solution.
Hi @AndrewPF ,
Pls refer the below:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUN7DUNzJUitXB5BgaEMcxJINjpK+AwsPBASIjIzROLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Date"}, {{"Count", each _, type table [Date=nullable date]}}),
#"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1, Int64.Type),
#"Expanded Count" = Table.ExpandTableColumn(#"Added Index", "Count", {"Date"}, {"Count.Date"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Count",{"Count.Date"})
in
#"Removed Columns"
Before:
After:
You could download my pbix file if you need.
Did I answer your question? Mark my post as a solution!
Best Regards
Lucien
You could always add a table in Power Query with a distinct list of dates and then add the index. Merge your table with the distinct list and return the index column...at least you know the data is persisted and you won't experience weird behaviour with calculated columns and filter context.
Hi @AndrewPF ,
Pls refer the below:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUN7DUNzJUitXB5BgaEMcxJINjpK+AwsPBASIjIzROLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Date"}, {{"Count", each _, type table [Date=nullable date]}}),
#"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1, Int64.Type),
#"Expanded Count" = Table.ExpandTableColumn(#"Added Index", "Count", {"Date"}, {"Count.Date"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Count",{"Count.Date"})
in
#"Removed Columns"
Before:
After:
You could download my pbix file if you need.
Did I answer your question? Mark my post as a solution!
Best Regards
Lucien
'Group By' using date with 'all rows' as aggregation.
Add Index column.
Expand All column to bring back fields
Not sure if you can do that in Power Query but in DAX you could use RANKX, e.g.
Ranking = RANKX( 'Table', 'Table'[Date],, ASC, Dense)
Hi JohnT,
I tried it but I keep getting the messge: "a single value for column 'Date' in table '[table name]' cannot be determined.
Any idea?
It needs to be a column not a measure
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |