Excel Formatting Help!

[Anonymous]

Hello!

I am lookiing for some excel help or tips! I currently am reciveving data i the following form. I eventually am going to to be sorting the information by building. I am looking for an efficient way to combine each row to eventually contain all of the values in one row ( to eventually be put in powerbi). I can go through and indivdually change each row but I will be recieving many years of data and this will talk an INSANE amount of time. 

Any tips, tricks, or comments could be extremly useful. Thanks!! 

 

[v-juanli-msft · Accepted solution]

Hi @Anonymous

For a simple dataset which i use for a test, you can easily get something like your screenshot by using a matrix.

But, if you want to make some changes on the data model, you could go to Query Editor

1.Duplicate Column of “commodify”

2.AddIndexColumn

3.pivot column “commodify” based on “total use”

4.Rename Columns with “use” as suffix.

5.pivot column “copy of commodify” based on “total demend”

6.Remove Columns which is all null value and “index” column after step5

Please refer to my pbix (look at sheet3)

Best Regards

Maggie

[Anonymous]

Thanks Maggie, this  was super helpful ... AND worked! 

[jthomson]

Still seems pretty unclear but I'd guess you can just make some measures to sum where you need to sum and drop everything into  a matrix visualisation

[jthomson]

It's not clear exactly what you're wanting to combine, what's your desired end result? You could probably find that a lot of what you want to do could be done in Power BI itself

[Anonymous]

I apologize, it isn't very clear. Eventually I want to be able to have the building data for each "commodity" formatted to one row and each commodity having it's own column like ...