Forum Discussion
Filter a table based on the MAX value from another query
Hi,
I have a problem where I have three tables and I want to filter, in the data load, two of the tables based on the MAX date in the third table. I have tried to used a parameter populated with the MAX date from a query, but it doesn´t seem to work since I can assign the current value the MAX date.
I would lika to create a variable that I can populate the MAX date, is that possible? If not, is there any workaround?
Rgs,
//Patrick
Hi PatrickB,
You could achieve this requirement via creating a function which always returns Max date from one table.
After loading the first table, right click its date column and choose "Add as a New Query". Then, you will get a date list.
Convert this new query to a function in Advanced Editor.
()=> let Source = Excel.Workbook(File.Contents("C:\Users\xxxx\Desktop\Sample Data2.xlsx"), null, true), Append1_Sheet = Source{[Item="Append1",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Append1_Sheet, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"HeadCount Month", type date}, {"HeadCount", Int64.Type}, {"Attribution Number", Int64.Type}}), #"HeadCount Month1" = #"Changed Type"[HeadCount Month], #"Max Date" = List.Max(#"HeadCount Month1",1) in #"Max Date"Invoke this function, then, you will get a invoked function (renamed it as GetMaxDate) in the left pane.
Now, you can load the second and third table. Modify their query code similar to below, in order to filter date records.
let Source = Excel.Workbook(File.Contents("C:\Users\xxxx\Desktop\Sample Data2.xlsx"), null, true), Append1_Sheet = Source{[Item="Append1",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Append1_Sheet, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"HeadCount Month", type date}, {"HeadCount", Int64.Type}, {"Attribution Number", Int64.Type}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [HeadCount Month] = GetMaxDate) in #"Filtered Rows"Best regards,
Yuliana Gu
2 Replies
Hi PatrickB,
You could achieve this requirement via creating a function which always returns Max date from one table.
After loading the first table, right click its date column and choose "Add as a New Query". Then, you will get a date list.
Convert this new query to a function in Advanced Editor.
()=> let Source = Excel.Workbook(File.Contents("C:\Users\xxxx\Desktop\Sample Data2.xlsx"), null, true), Append1_Sheet = Source{[Item="Append1",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Append1_Sheet, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"HeadCount Month", type date}, {"HeadCount", Int64.Type}, {"Attribution Number", Int64.Type}}), #"HeadCount Month1" = #"Changed Type"[HeadCount Month], #"Max Date" = List.Max(#"HeadCount Month1",1) in #"Max Date"Invoke this function, then, you will get a invoked function (renamed it as GetMaxDate) in the left pane.
Now, you can load the second and third table. Modify their query code similar to below, in order to filter date records.
let Source = Excel.Workbook(File.Contents("C:\Users\xxxx\Desktop\Sample Data2.xlsx"), null, true), Append1_Sheet = Source{[Item="Append1",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Append1_Sheet, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"HeadCount Month", type date}, {"HeadCount", Int64.Type}, {"Attribution Number", Int64.Type}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [HeadCount Month] = GetMaxDate) in #"Filtered Rows"Best regards,
Yuliana Gu
