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.
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?
Solved! Go to Solution.
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.
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.
Marcus,
I can sort of follow the logic from your file but if you can see the original sample data I provided, the merged data did not have a month field, only the building name and the score so I'm not entirely sure how to do the level of cross-filtering you've demonstrated via your file?
Kind regards,
Hi @Humblelistener ,
could you provide the "Base" sample data for the "Building statistics" and "Score"?
When I look at your merge data there is a month field...
... and you also described it accordingly.
Hello Marcus,
That's because after the merge, it associated a month to the Building Score.
This is what the Building Stats data looks like (which has months):
YEAR | QTR | MONTH | BUILDING | SQFT | REGION | MANAGER | STATUS | OCCUPANCY |
2019 | Q1 | Jan-19 | A | 5000 | Americas | Smith, John | Planned | 5 |
2019 | Q1 | Feb-19 | A | 5000 | Americas | Smith, John | Active | 11 |
2019 | Q1 | Mar-19 | A | 5000 | Americas | Smith, John | Active | 6 |
2019 | Q2 | Apr-19 | A | 5000 | Americas | Smith, John | Active | 7 |
2019 | Q2 | May-19 | A | 5000 | Americas | Smith, John | Active | 6 |
2019 | Q2 | Jun-19 | A | 5000 | Americas | Smith, John | Active | 11 |
2019 | Q3 | Jul-19 | A | 5000 | Americas | Smith, John | Active | 15 |
2019 | Q3 | Aug-19 | A | 5000 | Americas | Smith, John | Active | 10 |
2019 | Q3 | Sep-19 | A | 5000 | Americas | Smith, John | Active | 9 |
2019 | Q4 | Oct-19 | A | 5000 | Americas | Smith, John | Active | 8 |
2019 | Q4 | Nov-19 | A | 5000 | Americas | Lee, Rob | Active | 8 |
2019 | Q4 | Dec-19 | A | 5000 | Americas | Lee, Rob | Active | 6 |
2020 | Q1 | Jan-20 | A | 5000 | Americas | Lee, Rob | Active | 7 |
2019 | Q1 | Jan-19 | B | 9000 | Asia | Ho, Mike | Planned | 20 |
2019 | Q1 | Feb-19 | B | 9000 | Asia | Ho, Mike | Active | 18 |
2019 | Q1 | Mar-19 | B | 9000 | Asia | Ho, Mike | Active | 22 |
2019 | Q2 | Apr-19 | B | 9000 | Asia | Ho, Mike | Active | 24 |
2019 | Q2 | May-19 | B | 9000 | Asia | Ho, Mike | Active | 19 |
2019 | Q2 | Jun-19 | B | 9000 | Asia | Ho, Mike | Active | 20 |
2019 | Q3 | Jul-19 | B | 9000 | Asia | Ho, Mike | Active | 23 |
2019 | Q3 | Aug-19 | B | 9000 | Asia | Ho, Mike | Active | 26 |
2019 | Q3 | Sep-19 | B | 9000 | Asia | Ho, Mike | Active | 21 |
2019 | Q4 | Oct-19 | B | 9000 | Asia | Ho, Mike | Active | 20 |
2019 | Q4 | Nov-19 | B | 9000 | Asia | Ho, Mike | Active | 22 |
2019 | Q4 | Dec-19 | B | 9000 | Asia | Ho, Mike | Active | 21 |
2020 | Q1 | Jan-20 | B | 9000 | Asia | Ho, Mike | Active | 22 |
2019 | Q1 | Jan-19 | C | 6800 | Europe | Rustov, Vlad | Planned | 14 |
2019 | Q1 | Feb-19 | C | 6800 | Europe | Rustov, Vlad | Active | 16 |
2019 | Q1 | Mar-19 | C | 6800 | Europe | Rustov, Vlad | Active | 17 |
2019 | Q2 | Apr-19 | C | 6800 | Europe | Rustov, Vlad | Active | 19 |
2019 | Q2 | May-19 | C | 6800 | Europe | Rustov, Vlad | Active | 20 |
2019 | Q2 | Jun-19 | C | 6800 | Europe | Rustov, Vlad | Active | 11 |
2019 | Q3 | Jul-19 | C | 6800 | Europe | Rustov, Vlad | Active | 12 |
2019 | Q3 | Aug-19 | C | 6800 | Europe | Rustov, Vlad | Active | 12 |
2019 | Q3 | Sep-19 | C | 6800 | Europe | Oro, Miguel | Active | 14 |
2019 | Q4 | Oct-19 | C | 6800 | Europe | Oro, Miguel | Active | 16 |
2019 | Q4 | Nov-19 | C | 6800 | Europe | Oro, Miguel | Active | 17 |
2019 | Q4 | Dec-19 | C | 6800 | Europe | Oro, Miguel | Active | 19 |
2020 | Q1 | Jan-20 | C | 6800 | Europe | Oro, Miguel | Active | 20 |
This is what the Scores data looks like:
YEAR | QTR | BUILDING | SCORE |
2019 | Q2 | A | 23 |
2019 | Q3 | A | 23 |
2019 | Q4 | A | 23 |
2020 | Q1 | A | 26 |
2019 | Q3 | B | 18 |
2019 | Q4 | B | 18 |
2020 | Q1 | B | 28 |
2019 | Q4 | C | 22 |
2020 | Q1 | C | 23 |
So, the merge is based on the matching the Year, Quarter, and Building Name. So the merged data looks like this:
YEAR | QTR | MONTH | BUILDING | SQFT | REGION | MANAGER | STATUS | OCCUPANCY | Merged.Building | Merged.Score |
2019 | Q1 | Jan-19 | A | 5000 | Americas | Smith, John | Planned | 5 | null | null |
2019 | Q1 | Feb-19 | A | 5000 | Americas | Smith, John | Active | 11 | null | null |
2019 | Q1 | Mar-19 | A | 5000 | Americas | Smith, John | Active | 6 | null | null |
2019 | Q2 | Apr-19 | A | 5000 | Americas | Smith, John | Active | 7 | A | 23 |
2019 | Q2 | May-19 | A | 5000 | Americas | Smith, John | Active | 6 | A | 23 |
2019 | Q2 | Jun-19 | A | 5000 | Americas | Smith, John | Active | 11 | A | 23 |
2019 | Q3 | Jul-19 | A | 5000 | Americas | Smith, John | Active | 15 | A | 23 |
2019 | Q3 | Aug-19 | A | 5000 | Americas | Smith, John | Active | 10 | A | 23 |
2019 | Q3 | Sep-19 | A | 5000 | Americas | Smith, John | Active | 9 | A | 23 |
2019 | Q4 | Oct-19 | A | 5000 | Americas | Smith, John | Active | 8 | A | 23 |
2019 | Q4 | Nov-19 | A | 5000 | Americas | Lee, Rob | Active | 8 | A | 23 |
2019 | Q4 | Dec-19 | A | 5000 | Americas | Lee, Rob | Active | 6 | A | 23 |
2020 | Q1 | Jan-20 | A | 5000 | Americas | Lee, Rob | Active | 7 | A | 26 |
2019 | Q1 | Jan-19 | B | 9000 | Asia | Ho, Mike | Planned | 20 | null | null |
2019 | Q1 | Feb-19 | B | 9000 | Asia | Ho, Mike | Active | 18 | null | null |
2019 | Q1 | Mar-19 | B | 9000 | Asia | Ho, Mike | Active | 22 | null | null |
2019 | Q2 | Apr-19 | B | 9000 | Asia | Ho, Mike | Active | 24 | null | null |
2019 | Q2 | May-19 | B | 9000 | Asia | Ho, Mike | Active | 19 | null | null |
2019 | Q2 | Jun-19 | B | 9000 | Asia | Ho, Mike | Active | 20 | null | null |
2019 | Q3 | Jul-19 | B | 9000 | Asia | Ho, Mike | Active | 23 | B | 18 |
2019 | Q3 | Aug-19 | B | 9000 | Asia | Ho, Mike | Active | 26 | B | 18 |
2019 | Q3 | Sep-19 | B | 9000 | Asia | Ho, Mike | Active | 21 | B | 18 |
2019 | Q4 | Oct-19 | B | 9000 | Asia | Ho, Mike | Active | 20 | B | 18 |
2019 | Q4 | Nov-19 | B | 9000 | Asia | Ho, Mike | Active | 22 | B | 18 |
2019 | Q4 | Dec-19 | B | 9000 | Asia | Ho, Mike | Active | 21 | B | 18 |
2020 | Q1 | Jan-20 | B | 9000 | Asia | Ho, Mike | Active | 22 | B | 28 |
2019 | Q1 | Jan-19 | C | 6800 | Europe | Rustov, Vlad | Planned | 14 | null | null |
2019 | Q1 | Feb-19 | C | 6800 | Europe | Rustov, Vlad | Active | 16 | null | null |
2019 | Q1 | Mar-19 | C | 6800 | Europe | Rustov, Vlad | Active | 17 | null | null |
2019 | Q2 | Apr-19 | C | 6800 | Europe | Rustov, Vlad | Active | 19 | null | null |
2019 | Q2 | May-19 | C | 6800 | Europe | Rustov, Vlad | Active | 20 | null | null |
2019 | Q2 | Jun-19 | C | 6800 | Europe | Rustov, Vlad | Active | 11 | null | null |
2019 | Q3 | Jul-19 | C | 6800 | Europe | Rustov, Vlad | Active | 12 | null | null |
2019 | Q3 | Aug-19 | C | 6800 | Europe | Rustov, Vlad | Active | 12 | null | null |
2019 | Q3 | Sep-19 | C | 6800 | Europe | Oro, Miguel | Active | 14 | null | null |
2019 | Q4 | Oct-19 | C | 6800 | Europe | Oro, Miguel | Active | 16 | C | 22 |
2019 | Q4 | Nov-19 | C | 6800 | Europe | Oro, Miguel | Active | 17 | C | 22 |
2019 | Q4 | Dec-19 | C | 6800 | Europe | Oro, Miguel | Active | 19 | C | 22 |
2020 | Q1 | Jan-20 | C | 6800 | Europe | Oro, Miguel | Active | 20 | C | 23 |
So, the Month is derived from the Building data. Hope this helps explain things. Thanks!
Hi @Humblelistener,
check this.
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.
I've tried to mimic the file you provided to the actual datasets that I'm working and I'm not getting the desired results. I guess I'm not following the logic around the steps used? Would you be so kind to break out the steps and what it accomplishes?
VERY MUCH APPRECIATED!!!
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.
Hi @Humblelistener ,
found a much simpler variant today.
Use Fill: https://www.excelcampus.com/library/fill-down-blank-null-cells-power-query/
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.
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |