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 need to change a query from table A to table B as below.
Basically I need to get the difference between the latest and the second latest date for each product ID.
Is there any way to do this in Advanced Editor?
Solved! Go to Solution.
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIyMDLUBUKlWB1kASMsIsYQISOIkJGuqS6agLmuOaqABboKoICBUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product ID" = _t, #"Sold Date" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Product ID", Int64.Type}, {"Sold Date", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Product ID"}, {{"Gap between last 2 days", each Duration.Days(List.Last(_[Sold Date])-List.First(List.LastN(_[Sold Date],2))), Int64.Type}})
in
#"Grouped Rows"
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIyMDLUBUKlWB1kASMsIsYQISOIkJGuqS6agLmuOaqABboKoICBUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product ID" = _t, #"Sold Date" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Product ID", Int64.Type}, {"Sold Date", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Product ID"}, {{"Gap between last 2 days", each Duration.Days(List.Last(_[Sold Date])-List.First(List.LastN(_[Sold Date],2))), Int64.Type}})
in
#"Grouped Rows"
It works great! Thank you.
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 |
---|---|
101 | |
50 | |
19 | |
12 | |
11 |