Forum Discussion
Efficient Method to Dynamically Pull Data Based on Calculated Percentage in Power BI
Hi Power BI Community,
I have a SQL data source that contains the most recent data and the last 5 historical runs of data, each specified with a run date.
Here's what I need to achieve in Power BI:
- Pull in the most recent run of data if a calculated percentage is within 5% of the last calculated percentage.
- If the calculated percentage exceeds 5%, pull in the run date prior to the most recent one.
Could anyone suggest the most efficient way to implement this logic in Power BI? Any guidance on Power Query steps, or other methods would be greatly appreciated!
From the information I have seen, it looks like I can do this w/ a parameter using power query but I'm still very confused on how to start. Do I need to create a seperate table with the run date and % so that I can use that as a criteria?
Thank you in advance for your help!
Hi JennyVill
Thank you very much lbendlin for your prompt reply.
For your question, here is the method I provided:
Here's some dummy data
Load your data. Make sure your table contains the run date and percentage columns.
“Table”
Open the advanced editor and write the M code:
let Source = ..., YourTable = Table.TransformColumnTypes(Source,{{"RunDate", type date}, {"Percentage", type number}}), // Sort by RunDate descending SortedData = Table.Sort(YourTable, {{"RunDate", Order.Descending}}), // Add an index column to identify the order of each run IndexedData = Table.AddIndexColumn(SortedData, "Index", 0, 1, Int64.Type), AddPercentageDiff = Table.AddColumn(IndexedData, "PercentageDiff", each if [Index] = 0 then null else Number.Abs(IndexedData{[Index]-1}[Percentage] - [Percentage]) * 100), AddCustomColumn = Table.AddColumn(AddPercentageDiff, "SelectedRun", each if [Index] = 0 or ([PercentageDiff] <= 5 ) then [RunDate] else IndexedData{[Index]-1}[RunDate]), // Filter based on custom columns FilteredData = Table.SelectRows(AddCustomColumn, each [SelectedRun] <> null), // Select the columns you want to keep RemoveColumns = Table.SelectColumns(FilteredData, {"RunDate", "Percentage", "SelectedRun"}) in RemoveColumnsHere is the result.
Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
You have two options
1. Implement your entire logic in SQL and present the result to Power Query as a view or SP
2. Ingest all the snapshots into Power Query and then throw away what you don't need.
Option 1 is the most efficient way.
Thank you so much for the reply! We're going to see if the logic can be implemented in SQL first and see if that works 🙂
Hi JennyVill
Thank you very much lbendlin for your prompt reply.
For your question, here is the method I provided:
Here's some dummy data
Load your data. Make sure your table contains the run date and percentage columns.
“Table”
Open the advanced editor and write the M code:
let Source = ..., YourTable = Table.TransformColumnTypes(Source,{{"RunDate", type date}, {"Percentage", type number}}), // Sort by RunDate descending SortedData = Table.Sort(YourTable, {{"RunDate", Order.Descending}}), // Add an index column to identify the order of each run IndexedData = Table.AddIndexColumn(SortedData, "Index", 0, 1, Int64.Type), AddPercentageDiff = Table.AddColumn(IndexedData, "PercentageDiff", each if [Index] = 0 then null else Number.Abs(IndexedData{[Index]-1}[Percentage] - [Percentage]) * 100), AddCustomColumn = Table.AddColumn(AddPercentageDiff, "SelectedRun", each if [Index] = 0 or ([PercentageDiff] <= 5 ) then [RunDate] else IndexedData{[Index]-1}[RunDate]), // Filter based on custom columns FilteredData = Table.SelectRows(AddCustomColumn, each [SelectedRun] <> null), // Select the columns you want to keep RemoveColumns = Table.SelectColumns(FilteredData, {"RunDate", "Percentage", "SelectedRun"}) in RemoveColumnsHere is the result.
Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you so much! I will try this method and let you know 🙂
