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 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
Solved! Go to Solution.
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
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
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 |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |