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.
Most Valuable ProfessionalHi 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.
Microsoft EmployeeI'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
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.