Forum Discussion

Microsoft Employee

6 years ago

Solved

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 ...

Need Help

mwegener
6 years ago
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.

mwegener

Most Valuable Professional

6 years ago

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

Microsoft Employee

6 years ago

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!

mwegener
Most Valuable Professional
6 years ago
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.
- Humblelistener
  Microsoft Employee
  6 years ago
  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!!!
  - mwegener
    Most Valuable Professional
    6 years ago
    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.

Forum Discussion

In a table after merged query, replace null column value with an existing value for an entity?

Related content

Replace existing image with another

How to replace Data Source for existing report?

Replace Value if Column exists

Why Power BI Deployment Pipelines Don't Replace Existing Reports in Apps

Replace NULL