Forum Discussion
How to reference another filtered table with a related field Power Query (M)
- 4 years ago
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("i45WMjIwtNQ1MFTSAbIMlGJ1YCJGQBFjFBFjoIgJSCQWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"YYYY-WW" = _t, #"Units Sold" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Units Sold", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Week Start Date", each List.Min(Table.SelectRows(Calendar, (x)=> x[#"YYYY-WW"]=[#"YYYY-WW"] )[Date])) in #"Added Custom"Code for Calendar
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc89CsMwDIbhu3hOsSXnx75At04JZAgZCg20Swulvn9MW4mAvkUgPWh4l8UR+xg8B0qucWN51lmXfArk1ubHJHx5GfZfzPU8lc0gC87bzWDUz3sx2Aqe3w+DneB4/RjsFQ8x/MdB8JgimFCKYEYpghRQiyqhGFVGNaoR5UTRFvWodihItUdFqgMqUk2oSDWjoqrrDg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Day = _t, #"YYYY-WW" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}) in #"Changed Type"
Hi lvhebert
Here is another method for your reference. You can use the user interface to get the expected result easily.
1. In Sales table, click on Home > Merge Queries. In Merge window, select YYYY-WW column as matching column. Select Calendar table to be merged to Sales table. Select Left Outer for join kind. Click OK.
2. Click expand icon on column header of above merged table column. In the pop-up pane, switch to Aggregate. By default, it may show # Count of Date in the list. Hover over it and click the down arrow on right side and select Minumum. Then it will change to Min of Date. Click OK.
3. Now you will have the expected result.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.