Forum Discussion
Microsoft EmployeeIn a table after merged query, replace null column value with an existing value for an entity?
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.
Microsoft EmployeeHello 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!!!
Most Valuable ProfessionalHi 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.
- Humblelistener
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,
- mwegener
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.
- Humblelistener
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!