Forum Discussion
In a table after merged query, replace null column value with an existing value for an entity?
In Power BI Desktop, I've merged a two queries:
1) Building statistics over time
2) KPI metrics for each building but only contains recent data.
What I'm trying to accomplish is use the KPI metrics for the current period but for all of the building periods without this metric, to replace the null value with the current metric (as a benchmark value). Going forward as we capture this KPI, the column can contain the recent data. I originally thought I could use a conditional column but can't build the logic for each building, if value exists for that time period, use the value, if not, use the last value (most recent metric).
Is there a way I can accomplish this?
Hi Humblelistener ,
In the score query, the steps create a numeric column YEAR-QTR
Similarly, I create the same column YEAR-QTR in the Building Stats query
Merge the two queries using the BUILDING column...
...and expand the Scores YEAR-QTR.
The conditional column marks the relevant rows, where the Building Stats.YEAR-QTR <= Scores.YEAR-QTR
Than you can filter on the relevant rows.
Group the rows to get the last/nearest Scores.YEAR-QTR
Now you can merge the two queries related to the BUILDING and (the latest) Scores.YEAR-QTR columns.
Expand Scores column to get the Score value
Regards,
Marcus
Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
10 Replies
Hi Humblelistener ,
can you provide some sample data?
I think you have to do another merge with the last value, to use your conditional column.
Regards,
Marcus
Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.Hello Marcus,
Thank you for the reply! Here's a sample of the data.
Building Month Merged.Building Merged.Score
A 1/19 null null
A 2/19 null null
A 3/19 null null
A 4/19 null null
A 5/19 null null
A 6/19 null null
A 7/19 null null
A 8/19 null null
A 9/19 A 23
A 10/19 A 23
A 11/19 A 23
A 12/19 A 23
A 1/20 A 26
B 5/19 null null
B 6/19 null null
B 7/19 null null
B 8/19 null null
B 9/19 B 30
B 10/19 B 30
B 11/19 B 30
B 12/19 B 30
B 1/20 B 28
So basically there is a table with Building statistics broken out by month then merged columns from another table with a "Score". What I'd like to do is create a custom column called "Based Score" and for each building, for the months where there is a "score" value, to keep what is there (9/19 - 1/20) but for the months where there is no score (1/19 - 8/19), to replace the null value with the last month with a score (in this case, 9/19). Please note in the example above that building "B" is a newer building that came online as of 5/19 so the condition needs to be able to account for buildings with less than 12 months.
THANKS!!!
Hi Humblelistener ,
check this PBIX
Regards,
Marcus
Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
