Pivot one column related to multiple columns

[Victormar]

Hi all, and thanks in advance for the support,

I have collected data from a Sharepoint Online List, and as it is getting the data from power apps, we are getting different results for every day, meaning we can have multiple registers for the same date, depending on the machine (#01, #02...) used. My idea would be to convert the machine colum to multiple columns, and join the data so in the end I only have 1 row for each day. 

This is how I have it now: 

And I was thinking to have it like:

Were every "#0..." corresponds with the machine, and the value would be the Hour-meter and Machine hour values. 

I've tried pivoting the columns but then it would put the Machines in columns, but still maintain differente rows for each day, and just add "nulls" if it doesn't match with the machine measured:

That would apply both for Hour-meter and Machine-hour columns.

PD: I don't know if this is the best solution, as I know I can work with the data as it is now, but they had it set up this way with the old solution.

Thanks for the help 🙂

[AlexisOlson]

Pivoting works fine when I try it.

[Victormar]

Thanks for the answer 🙂

Yeah it works fine, but then I have multiple rows with the same date ( like 1/11/2022) and I would like to have just one with the values of each column, as the excel has. Is it possible?

[AlexisOlson · Accepted solution]

Before pivoting, you could group by Date and Machine (and any other relevant columns) and take the maximum over the Hour-meter column.

You could also, instead, group by Date after pivoting and take the maximum over each of the pivoted columns as I mentioned in this other post:
https://community.powerbi.com/t5/Power-Query/Unpivot-columns-while-connected-with-Direct-Query/m-p/2...

[Victormar]

Thanks! that was what I was looking for 🙂

[amitchandak]

@Victormar , You pivoted and machine and hour-meter ?

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Victormar]

Yes I pivoted it, the problem is that I get what I want, in terms of new columns with the data related to hours and machine hour, but then I get several rows for each day, matching each column, if that makes sense.

At that point I would need to merge all the day rows in just one, if that makes sense.