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 all,
How can I filter to only show the row with the latest date for each externalid below?
So only the row with 07/05/2022 for 3929 would show as it is the latest date.
Solved! Go to Solution.
Hi @HenryJS ,
Base data:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjazNFDSUXLNLcjJr0wtUvDJTEzKzMksqVTwzCsuLUrMS04FShsZGBnpm+obGivF6gD1WBpZAgUDivLTUouLM/PzEnOAqlNSc/Nw6DNH1obXBuJUGkJUxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [externalid = _t, doctype = _t, Expiry = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"externalid", Int64.Type}, {"doctype", type text}, {"Expiry", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"externalid"}, {{"allrows",each Table.AddIndexColumn(
Table.Sort(_,{{"Expiry",
Order.Ascending}}), "Row Rank",1,1), each _, type table [externalid=nullable number, doctype=nullable text, Expiry=nullable date]}}),
#"Expanded allrows" = Table.ExpandTableColumn(#"Grouped Rows", "allrows", {"externalid", "doctype", "Expiry", "Row Rank"}, {"allrows.externalid", "allrows.doctype", "allrows.Expiry", "allrows.Row Rank"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded allrows", each ([allrows.Row Rank] = 1))
in
#"Filtered Rows"
Final output:
refer:
https://data-witches.com/2020/10/21/adding-a-row-rank-based-on-a-different-column-with-power-query/
Best Regards
Lucien
The currently accepted solution depends on modifyin M code in the advanced editor. This is not necessary. The way to do it is to group on doctype and aggregate on the Max of Expiry, add an aggregate level called expansion and select all rows. Next step is to expand and add a conditional column with name "Latest?". Compare column Expiry with column Max of Expiry, and if equal, enter "latest", otherwise "null". Filter column "Latest?" on "latest". If necessary you can delete columns "Max of Expiry" and "Latest?", and you are done.
Hi @HenryJS ,
Base data:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjazNFDSUXLNLcjJr0wtUvDJTEzKzMksqVTwzCsuLUrMS04FShsZGBnpm+obGivF6gD1WBpZAgUDivLTUouLM/PzEnOAqlNSc/Nw6DNH1obXBuJUGkJUxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [externalid = _t, doctype = _t, Expiry = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"externalid", Int64.Type}, {"doctype", type text}, {"Expiry", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"externalid"}, {{"allrows",each Table.AddIndexColumn(
Table.Sort(_,{{"Expiry",
Order.Ascending}}), "Row Rank",1,1), each _, type table [externalid=nullable number, doctype=nullable text, Expiry=nullable date]}}),
#"Expanded allrows" = Table.ExpandTableColumn(#"Grouped Rows", "allrows", {"externalid", "doctype", "Expiry", "Row Rank"}, {"allrows.externalid", "allrows.doctype", "allrows.Expiry", "allrows.Row Rank"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded allrows", each ([allrows.Row Rank] = 1))
in
#"Filtered Rows"
Final output:
refer:
https://data-witches.com/2020/10/21/adding-a-row-rank-based-on-a-different-column-with-power-query/
Best Regards
Lucien
on your document table make a new colum.
where you write True/false pr line if this is the latest date for this External ID.
New colum:
Max_date_by_esternal_ID =
IF(
calculate(Max(Document[Date], filter(document, Document[ExternalID], Earlier([Document[ExternalID])))=Document[Date] ,
True(),
False()
)
the EARLIER will take the External ID from the Line it is currently on and use that as a filter to get the max date
NOW you have a TRUE / False Column you can use as a filter
Mine look like this and work
so you would get 3 True lines
since external id 3929 has 2 Expiry dates that are both the latest date avaliable for that Id
Hi,
if you want to filter in power query:
and it's done.
If this post isuseful to help you to solve your issue consider giving the post a thumbs up and accepting it as a solution !
Sorry,
i didn't understand your question.
If you are able to post some sample data (to do this you can select the colums and with right click select copy and then paste here) i think it is possible
@serpiva64 this only returns the latest date for doctype column
it does not return latest doctype for each externalid
how can i do this in Power Query?
Try something similar
CALCULATE (
MAX ( calendertable[Date] ),
FILTER (
ALL ( calendertable[Date] ),
salestable[TotalSales]>0
)
))
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 | |
100 | |
78 | |
75 | |
52 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |