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.
Dear all,
I want to create the table on the right from the table on the left below.
"Max Date and second-max content (not null) in Name with status Delete."
I created an All Rows column in PowerQuery's GroupBy and added a Table.Max expression in the custom column,
but I only get the row for the largest day and the content is null.
Using MaxN, I get two rows.MaxN will give me 2 rows.
I guess I need to apply more GroupBy, but I don't want to make it too complicated.
How can I get the right side of the table?
Thank you in advance.
Solved! Go to Solution.
Ah, the old moving target question.
If you are going to return all of the status's, then you would use a different algorithm.
Merely
let
//read in the data and set data types
//be sure to change table name in next line to actual table name
Source = Excel.CurrentWorkbook(){[Name="Table13"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{
{"Name", type text}, {"Status", type text}, {"Date", type date}, {"Content", type text}}),
//Group by name
#"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {
//extract status for max date
//if status "Delete" return content for next line
// else return status for the same line
{"Status", each Table.Sort(_,{"Date",Order.Descending})[Status]{0}, type text},
{"Date", each List.Max([Date]), type date},
{"Content", (t)=>
let
sorted=Table.Sort(t,{"Date",Order.Descending})
in
if sorted[Status]{0}="Delete" then sorted[Content]{1} else sorted[Content]{0},type text}
})
in #"Grouped Rows"
Can you not create a table that contains the status you are interested and perform a Right Anti join to give you records on the left table that exists on the right table.
You can easily do this with some custom aggregation in the Table.Group function.
You'll need to go into the Advanced Editor.
Please read the code comments.
If your actual data is significantly different from what you've posted, modifications will be required, but I believe the below will work with the kinds of variations I can think of.
If the data is exactly like what you post, the code can be simplified.
let
//read in the data and set data types
//be sure to change table name in next line to actual table name
Source = Excel.CurrentWorkbook(){[Name="Table13"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{
{"Name", type text}, {"Status", type text}, {"Date", type date}, {"Content", type text}}),
//Group by name
#"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {
//Extract the date for the Delete status
//in your examples, there is only one.
// If there are multiple may need to extract the Max date
// If there is no status=delete, then will return an error which we
// adjust for with the try..otherwise
{"Date", (t)=> try List.Max(Table.SelectRows(t,each [Status]="Delete")[Date]) otherwise null, type nullable date},
//return Delete (or null if delete not present in subtable)
{"Status", each if List.Contains([Status],"Delete") then "Delete" else null },
// Return [Content] from the second line of the date-sorted subtable
{"Content", (t)=>
let
maxDt = List.Max(Table.SelectRows(t,each [Status]="Delete")[Date]),
filterAndSort =
Table.Sort(
Table.SelectRows(t,each [Date] <= maxDt and [Status]<>"Delete"),
{"Date", Order.Descending})[Content]{0}
in filterAndSort}
}),
#"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Status] = "Delete"))
in
#"Filtered Rows"
Dear ronrsnfld,
Thank you very much. It's very helpful.
Please let me know if you can add more if you can make it this far.
If I want to add not only DELETE but also other statuses to the list,
How do I change the description in the advanced editor?
In that case, I want to list the maximum Status and Content of the Date.
Thanks in advance for your help!
Ah, the old moving target question.
If you are going to return all of the status's, then you would use a different algorithm.
Merely
let
//read in the data and set data types
//be sure to change table name in next line to actual table name
Source = Excel.CurrentWorkbook(){[Name="Table13"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{
{"Name", type text}, {"Status", type text}, {"Date", type date}, {"Content", type text}}),
//Group by name
#"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {
//extract status for max date
//if status "Delete" return content for next line
// else return status for the same line
{"Status", each Table.Sort(_,{"Date",Order.Descending})[Status]{0}, type text},
{"Date", each List.Max([Date]), type date},
{"Content", (t)=>
let
sorted=Table.Sort(t,{"Date",Order.Descending})
in
if sorted[Status]{0}="Delete" then sorted[Content]{1} else sorted[Content]{0},type text}
})
in #"Grouped Rows"
Dear ronrsnfld,
I was able to execute it perfectly.
It was very helpful for me.
Thank you very much!
BR
Oops. Thanks. I'll edit my response
Seems like you could do this in one step:
=Table.SelectRows(PriorStepOrTableName, each [Date] = List.Max(PriorStepOrTableName[Date]) or [Content] = List.MaxN(PriorStepOrTableName[Content], 2){1} and [Status] <> "Delete")
--Nate
Dear bf,
Thank you for your help!
I tried it. It worked.
But it would be nice if it could be done in one table,
is that not possible with this kind of data source?
BR
@umekuro In my opinion this is the only way you can do it, as you have to hook the content as if it were a vlookup with different filters than the rest of the table.
if you think my solution is correct, I ask you to accept it, thanks!
B.
Hi @umekuro !
Suppose that yout left table is named "Tabella", you have to create two other Tables, like this:
let
Origine = Tabella,
#"Filtrate righe" = Table.SelectRows(Origine, each ([Content] <> "null")),
#"Raggruppate righe" = Table.Group(#"Filtrate righe", {"Name"}, {{"Date", each List.Max([Date]), type nullable date}, {"Content", each List.Max([Content]), type nullable text}})
in
#"Raggruppate righe"
and the second one, which is your expected result (right table):
let
Origine = Tabella,
#"Filtrate righe" = Table.SelectRows(Origine, each ([Status] = "Delete")),
#"Raggruppate righe" = Table.Group(#"Filtrate righe", {"Name", "Status"}, {{"Date", each List.Max([Date]), type nullable date}}),
#"Merge di query eseguito" = Table.NestedJoin(#"Raggruppate righe", {"Name"}, #"Tabella (3)", {"Name"}, "Tabella (3)", JoinKind.LeftOuter),
#"Tabella Tabella (3) espansa" = Table.ExpandTableColumn(#"Merge di query eseguito", "Tabella (3)", {"Content"}, {"Tabella (3).Content"})
in
#"Tabella Tabella (3) espansa"
Try and tell me!
B.
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.