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

[Humblelistener]

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?

[mwegener]

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.

[Humblelistener]

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

[mwegener]

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]

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!

[mwegener]

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]

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

[mwegener]

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.