Combine values from different rows to allow duplicates to be removed

[Mandi-Holt]

I am fairly new to using Power BI and Power Query and have a table that has been created from doing a left outer join on a WeatherReading table and a CustomWeather table.

The new table has multiple rows with the same VslReadingId where there were multiple CustomWeather.Ids.

I need to have the CustomWeather.Key values as separate columns with the values for each in the row so the table appears as:

OperationName	Date	Visibility	WindGustRecorded AtCalculatedValue	Earthquake	TRV	Temp
Loadout	27/04/2021 00:00	50000	24.74	1	1

I have created custom columns for each value in the CustomWeather.Key (Earthquake, TRV and Temp) using the following formula for each column (there won't be many custom weather keys created so it is not a major issue to create the new columns individually each time one is added):

• Earthquake = IF [CustomWeather.Key] = “Earthquake” then [CustomWeather.Value] else null

This has then given me the following :

At this point I've become stuck through my lack of knowledge. I don't know how to have the individual CustomWeather.Value against each VlsReadingId so I can then remove the duplicate VslReadinId's and therefore pull a report grid together displaying all the weather readings along one row.

ie include all the Earthquake, TRV and Temp values in the correct columns and on each row where the VslReadingId is the same.

Any help will be gratefully recieved. 

Mandi

[Mandi-Holt · Accepted solution]

Just to update that this issue has been solved externally to this group. In case anyone else is looking to do the same then here's a couple of solutions.

Add a calculated table:

Table1 = 

SUMMARIZE (

AllWeatherReadings,

AllWeatherReadings[VslReadingId],

“Earthquake”, MAX ( AllWeatherReadings[Earthquake] ),

“TRV”, MAX ( AllWeatherReadings[TRV] ),

“Temp”, MAX ( AllWeatherReadings[Temp] )

The alternative is in Power Query to use the Group By, select advanced and add grouping by the VslReadingId column, name the columns the same, set them to Max and ok.

Thank you.