Log In
Register
Home
New Member
May 5, 2020
Offline
Hi all,
Power Query question here. In my merged query table attached in sheet 3, the "Weather" and "Weather Estimate" columns are sometimes null. I am trying to include some logic to those columns that if the column is null, to use the "Use Weather From" column to match a Site ID for the same day and use the Weather and Weather Estimate data from that site.
For example, on 10/2/21, Site 8 (site ID 50050) has null data for weather and weather estimate, so I'd want to take weather data from site 50078 for 10/2 for that day. In this case the answer would be 4939 and 4483.
I believe I either need to create a new custom column, or use the advanced editor under power query, or likely a combination of both but I think all would require BI coding that is lost on me.
Hope someone can help out! This would be so cool if it works!
Viktor
See Answer
October 5, 2010
Offline
Hi Viktor,
Attached is a file that fills in the blanks as requested.
There are a few issues though as there are some Site ID's where their Use Weather From is their own ID. SO if the Weather or Weather Estimate is null for those ID's, there's nothing to replace it with.
I've created a query called Problems that lists where this occurs.
You also have errors (Site Name is N/A) in your Production Data which are causing errors in the final query. If you fix the source the query errors will disappear.
Regards
Phil
Answers Post
New Member
May 5, 2020
Offline
Phil,
Really appreciate you getting back to me. I tried to re-create your work on my own, to ensure that I am learning the process. I think I understand what you mean with the errors, as my query results in errors and the formulas won't work. I am not sure how you overcame that, as I don't see an applied step of removing any errors. So I can't reproduce exactly as you have done.
In terms of the errors I think it's throwing, I suppose I should have given more detail. It's possible for the sensor that is producing the data to go down, so null values would be expected in that case. So, if the Site ID and Use Weather From columns match, and the value is still null, then null is fine.
It looks like your file is already doing that, which is great.
I think i'm close, but let me know if I went wrong somewhere.
The steps I did were:
1. Opened up query editor and added 3 custom columns, kept your source code (thank you!)
2. Got an error with Weather_Form, so added that as a query and then custom1 formula was happy
3. Applied columns but seeing errors in each when I hover over row 0 on the query editor. My custom columns are just a mirror of the originals.
Thank you!
October 5, 2010
Offline
Hi Viktor,
The errors are caused because you have rows in your Production Data where the Site Name is a #N/A error, for example Row 1831 to Row 1861. It's not related to any nulls.
In the Prod query, when the queries are merged, they merge based on matching Site ID. As you have rows where there are no site ID's - they are the #N/A errors - these rows do not appear in the merged table.
I think the code in this step may be confusing as I've named a table and a variable very similarly - WeatherFrom and Weather_From. So I've changed those to Weather_From_Table and WeatherFromID
= Table.AddColumn(#"Removed Columns", "Custom.1", each let
WeatherFromID = [Info.Use Weather From],
TS = [Timestamp]
in
if [Weather] is null or [Weather Estimate] is null then Table.SelectRows(Weather_From_Table, each [Site ID] = WeatherFromID and [Timestamp] = TS)
else [Weather])
After the queries are merged and the Table in the Info column is expanded you need to tidy up a little by removing the old Weather and Weather Estimate columns, and a couple of others.
See attached file for code.
Regards
Phil
1 Guest(s)
