Forum Discussion
Using Group-By based on MAX date column
This should work
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY8xC8JADIX/ynFzhbvr5KgFQRAd6iKlw1FDKZakJop/30unq/aRIQnvIy9NY73KFtb5TarggjNJaVEN3I2g3Rk+5kb8SO1lArRt8Y/5bQYtmWokgftMBdWCSkP9fEdWpo5oDhxRBukozUc0E1PPILJKB5fTe5IXYZ4x95fq96v+LF+pyi8oceUhYj9/dSIxO+xhBPnN134B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CountryCustomerCode = _t, #"Date (B)" = _t, Name = _t, #"Current city" = _t, #"Status " = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date (B)", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"CountryCustomerCode"}, {{"AA", each let t =_ in Table.SelectRows(t, each [#"Date (B)"] = List.Max(t[#"Date (B)"])), type table }}), #"Expanded AA" = Table.ExpandTableColumn(#"Grouped Rows", "AA", {"Date (B)", "Name", "Current city", "Status "}, {"Date (B)", "Name", "Current city", "Status "}) in #"Expanded AA"My advice is to avoid spaces in ColumnNames and Step/TableNames, otherwise you have to use #"".
Thank you very much, it seems to work!
Hi, the solution here actually works, it's a simple way to do it and doesn't crash my Power BI!
Yes, I supposed that your grouping column would be the 1st column according to sample you provided.
You can change List.RemoveFirst... part to
List.RemoveItems(Table.ColumnNames(Source), {"CountryCustomerCode"})
or provide proper sample data.
Or just do grouping first then expand later with columns you need. I merged those 2 steps...
Hi Jakinta , thanks for your response! I can also just move the column to be the first one in my data - no problems.
I am not super skilled on the Power Query coding so I am not exactly sure how I would modify the code. Even though I move the column I get the same error.
Also if I try to modify the code I get the same errors (probably I am doing something wrong):
let
Source = #"(B) Main Database",
#"ChangedType" = Table.TransformColumnTypes(Source,{{"(B) Date", type date}}),
Grouped = Table.ExpandTableColumn(Table.Group(#"ChangedType", {"CountryCustomerCode"}, {{"A", each let t=_ in Table.SelectRows(t, each [Date]= List.Max(t[Date]))}}), "A",
List.RemoveItems(Table.ColumnNames(Source), {"CountryCustomerCode"}))
in
Grouped
So my sample data looks like this now:
| CountryCustomerCode | Date (B) | Name | Current city | Status |
| 11111 | 01-01-2020 | Circle | New York | Open |
| 11111 | 01-01-2019 | Circle | New York | Closed |
| 22222 | 01-01-2019 | Square | San Fransisco | In progress |
| 22222 | 01-01-2020 | Square | Boston | Open |
| 22222 | 01-03-2021 | Square | Boston | Closed |
| 33333 | 01-01-2021 | Triangle | Los Angeles | In progress |
This should work
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY8xC8JADIX/ynFzhbvr5KgFQRAd6iKlw1FDKZakJop/30unq/aRIQnvIy9NY73KFtb5TarggjNJaVEN3I2g3Rk+5kb8SO1lArRt8Y/5bQYtmWokgftMBdWCSkP9fEdWpo5oDhxRBukozUc0E1PPILJKB5fTe5IXYZ4x95fq96v+LF+pyi8oceUhYj9/dSIxO+xhBPnN134B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CountryCustomerCode = _t, #"Date (B)" = _t, Name = _t, #"Current city" = _t, #"Status " = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date (B)", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"CountryCustomerCode"}, {{"AA", each let t =_ in Table.SelectRows(t, each [#"Date (B)"] = List.Max(t[#"Date (B)"])), type table }}), #"Expanded AA" = Table.ExpandTableColumn(#"Grouped Rows", "AA", {"Date (B)", "Name", "Current city", "Status "}, {"Date (B)", "Name", "Current city", "Status "}) in #"Expanded AA"My advice is to avoid spaces in ColumnNames and Step/TableNames, otherwise you have to use #"".
Thank you very much, it seems to work!