Forum Discussion

lvhebert's avatar
lvhebert
New Member
4 years ago
Solved

How to reference another filtered table with a related field Power Query (M)

I have two tables, one with sales data and one with a list of calendar dates. The date field in my sales data is formatted as YYYY-WW (2019-01 being week 1 of 2019). My calendar file has a list of all calendar dates with a another column giving the YYYY-WW classification.

 

I want to pull in the minimum date (Week Start) from the date column in my calendar where it matches the YYYY-WW in my sales data. 

 

I am appending this data with other files that contain the actuals dates, which is why I want to do it in Power Query instead of using DAX within the model. 

 

Sales Data

YYYY-WWUnits Sold
2019-01200
2019-02300
2019-03400

 

Calendar Data

DateYYYY-WW
12/30/18 Sun2019-01
12/31/18 Mon2019-01
01/01/19 Tue2019-01
01/02/19 Wed2019-01
01/03/19 Thu2019-01
01/04/19 Fri2019-01
01/05/19 Sat2019-01
01/06/19 Sun2019-02
01/07/19 Mon2019-02
01/08/19 Tue2019-02
01/09/19 Wed2019-02
01/10/19 Thu2019-02
01/11/19 Fri2019-02
01/12/19 Sat2019-02
01/13/19 Sun2019-03
01/14/19 Mon2019-03
01/15/19 Tue2019-03
01/16/19 Wed2019-03
01/17/19 Thu2019-03
01/18/19 Fri2019-03
01/19/19 Sat2019-03

 

Sales Data with new column

YYYY-WWUnits SoldWeek Start Date
2019-0120012/30/18
2019-0230001/06/19
2019-0340001/13/19

 

Greg_Deckler 

Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    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"

4 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Icon for Most Valuable Professional rankMost Valuable Professional
    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"
    • lvhebert's avatar
      lvhebert
      New Member
      4 years ago

      Thanks that worked! How do I adapt my existing queries to work as well?

      Calendar: 

      let
      Source = Excel.Workbook(File.Contents("C:\Users\lisa.hebert\OneDrive - Highline Aftermarket\Retail\Calendars\Retail Calendar.xlsx"), null, true),
      Calendar_Table = Source{[Item="Calendar",Kind="Table"]}[Data],
      #"Changed Type" = Table.TransformColumnTypes(Calendar_Table,{{"Date", type date}, {"Fiscal Year #", Int64.Type}, {"Fiscal Quarter", type text}, {"Fiscal Period #", Int64.Type}, {"Fiscal Week #", Int64.Type}, {"Fiscal YYYY-WW", type text}, {"Fiscal YYYY-PP", type date}, {"Fiscal YYYY-QQ", type text}, {"Week End", type date}, {"LY Week End", type date}, {"L1 Start", type date}, {"LY L1 Start", type date}, {"L4 Start", type date}, {"LY L4 Start", type date}, {"L12 Start", type date}, {"LY L12 Start", type date}, {"L52 Start", type date}, {" LY L52 Start", type date}, {"Fiscal Year Start", type date}, {"LY Fiscal Year Start", type date}, {"Month End Date", type date}, {"Month Start Date", type date}, {"Quarter End Date", type date}, {"Quarter Start Date", type date}, {"Year End Date", type date}, {"Year Start Date", type date}, {"Prior Month End Date", type date}, {"Prior Month Start Date", type date}, {"Prior Quarter End Date", type date}, {"Prior Quarter Start Date", type date}, {"Prior Year End Date", type date}, {"Prior Year Start Date", type date}, {"Current Trailing Month End Date", type date}, {"Current T12M Start Date", type date}, {"Current T3M Start Date", type date}, {"Current T1M Start Date", type date}, {"Prior Trailing Month End Date", type date}, {"Prior T12M Start Date", type date}, {"Prior T3M Start Date", type date}, {"Prior T1M Start Date", type date}, {"AZO Fiscal Year #", Int64.Type}, {"AZO Fiscal Week #", Int64.Type}, {"AZO Fiscal Period #", Int64.Type}, {"AZO Fiscal Week", Int64.Type}, {"AZO Fiscal Week1", type text}, {"AZO Fiscal Week2", type text}, {"AZO Fiscal Week3", type text}, {"AZO Fiscal Period", Int64.Type}, {"AZO Fiscal Period1", type text}, {"AZO Fiscal Period2", type text}, {"AZO Fiscal Quarter", Int64.Type}, {"AZO Fiscal Quarter1", type text}, {"AZO Fiscal Quarter2", type text}})
      in
      #"Changed Type"

       

      Sales:

      let
      Source = Folder.Files("C:\Users\lisa.hebert\OneDrive - Highline Aftermarket\Retail\ADV\Sales by Week\Sales By Week Data"),
      #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
      #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
      #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
      #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
      #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
      #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Fiscal Year", Int64.Type}, {"Fiscal Week In Year", Int64.Type}, {"Product SKU", type text}, {"Part #", type text}, {"Product Desc", type text}, {"Brand Desc", type text}, {"Retail Price", type number}, {"Product Cost", type number}, {"Merchandise Department Desc", type text}, {"Merchandise Class Desc", type text}, {"Merchandise SubClass Desc", type text}, {"Stocking Location Desc", type text}, {"Primary Vendor Name", type text}, {"Primary Vendor Code", Int64.Type}, {"Group Category Manager Name", type text}, {"Net Sales $", type number}, {"POS Net Qty", Int64.Type}, {"Gross Margin $", type number}, {"Gross Margin %", type number}}),
      #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Brand Desc", "Merchandise Department Desc", "Merchandise Class Desc", "Merchandise SubClass Desc", "Stocking Location Desc", "Group Category Manager Name", "Gross Margin $", "Gross Margin %", "Product Desc", "Source.Name"}),
      #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Product SKU", "SKU"}, {"POS Net Qty", "Net Retail Units"}, {"Net Sales $", "Net Retail $"}, {"Primary Vendor Code", "Vendor #"}, {"Primary Vendor Name", "Vendor Name"}, {"Product Cost", "Cost"}}),
      #"Added Custom" = Table.AddColumn(#"Renamed Columns", "YYYY-WW", each Number.ToText([Fiscal Year], "d1") & "-" &
      Number.ToText([Fiscal Week In Year], "d2"))
      in
      #"Added Custom"

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    4 years ago

    If you have large data, then a merge will be much faster than calculating Table.SelectRows for each date.

    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,{{"YYYY-WW", type date}, {"Units Sold", Int64.Type}}),
    CalendarSummary = Table.Group(CalendarData, "YYYY-WW", {"Week Start Date", each List.Min([Date]), type date}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"YYYY-WW"}, CalendarSummary, {"YYYY-WW"}, "Merge", JoinKind.LeftOuter),
    #"Expanded Merge" = Table.ExpandTableColumn(#"Merged Queries", "Merge", {"Week Start Date"}, {"Week Start Date"})
in
    #"Expanded Merge"

     

    You could modify your specific query along these lines:

    let
    Source = Folder.Files("C:\Users..."), 
    /*[...Intermediate steps here. Code redacted for brevity...]*/
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "YYYY-WW", each Number.ToText([Fiscal Year], "d1") & "-" &
    Number.ToText([Fiscal Week In Year], "d2")),
    /*New code starts here*/
    /*Use the name of your calendar table query instead of CalendarTable*/
    CalendarSummary = Table.Group(CalendarTable, "YYYY-WW", {"Week Start Date", each List.Min([Date]), type date}),
    /*The #"Added Custom" below references back to the last step from your original query.*/
    #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"YYYY-WW"}, CalendarSummary, {"YYYY-WW"}, "Merge", JoinKind.LeftOuter),
    #"Expanded Merge" = Table.ExpandTableColumn(#"Merged Queries", "Merge", {"Week Start Date"}, {"Week Start Date"})
in
    #"Expanded Merge"
  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    4 years ago

    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.