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.
Hi,
I am struggling to solve a problem in Power Query. Could someone help me?
My table look like this:
DATE | XXX |
05/01/2021 | 102 |
04/01/2021 | 115 |
04/01/2021 | 102 |
03/01/2021 | 102 |
01/01/2021 | 115 |
And i need that return this: The lastest date of xxx if they are in a sequence
DATE | XXX |
05/01/2021 | 102 |
04/01/2021 | 115 |
01/01/2021 | 115 |
Thanks in advance.
Solved! Go to Solution.
Hi @DimasArend,
This is what I understood, let's see if I got it right.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDVNzDUNzIwMlTSUTI0MFKK1QEKmiALGppiEYSpNMYmaIiuPRYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DATE = _t, XXX = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"DATE", type date}, {"XXX", Int64.Type}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"XXX", Order.Ascending}, {"DATE", Order.Ascending}}),
AddedIndex = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(AddedIndex, "Custom", each let
Check1 = try Duration.Days(AddedIndex[DATE]{[Index]} - AddedIndex[DATE]{[Index]-1}) otherwise 0,
Check2 = try AddedIndex[XXX]{[Index]} = AddedIndex[XXX]{[Index]-1} otherwise false
in
if Check1 = 1 and Check2 = true then null else [DATE], type date),
#"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
#"Grouped Rows" = Table.Group(#"Filled Down", {"XXX", "Custom"}, {{"DATE", each List.Max([DATE]), type nullable date}}),
#"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"DATE", "XXX"}),
#"Sorted Rows1" = Table.Sort(#"Removed Other Columns",{{"DATE", Order.Descending}})
in
#"Sorted Rows1"
Hi @DimasArend,
This is what I understood, let's see if I got it right.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDVNzDUNzIwMlTSUTI0MFKK1QEKmiALGppiEYSpNMYmaIiuPRYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DATE = _t, XXX = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"DATE", type date}, {"XXX", Int64.Type}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"XXX", Order.Ascending}, {"DATE", Order.Ascending}}),
AddedIndex = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(AddedIndex, "Custom", each let
Check1 = try Duration.Days(AddedIndex[DATE]{[Index]} - AddedIndex[DATE]{[Index]-1}) otherwise 0,
Check2 = try AddedIndex[XXX]{[Index]} = AddedIndex[XXX]{[Index]-1} otherwise false
in
if Check1 = 1 and Check2 = true then null else [DATE], type date),
#"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
#"Grouped Rows" = Table.Group(#"Filled Down", {"XXX", "Custom"}, {{"DATE", each List.Max([DATE]), type nullable date}}),
#"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"DATE", "XXX"}),
#"Sorted Rows1" = Table.Sort(#"Removed Other Columns",{{"DATE", Order.Descending}})
in
#"Sorted Rows1"
I don't fully understand what you're trying to get to.
Why is 115 in your result twice and 102 not?
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
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.