Forum Discussion
How to reference another filtered table with a related field Power Query (M)
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"
Most Valuable ProfessionalSee 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"
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"