Forum Discussion
Difficulty transforming data on PowerQuery
You can do this in Power Query.
For each Metro you will need to
-select the year column and unpivot other columns
-select the columns with year and month and merge them together
-in the resulting column you will need to Parse the date
-now add a column that is the name of the metro
you should end up with something that looks like (you can rename the columns as you wish)
-repeat those steps for every metro that you have
-append queries as new, selecting all of the metros (this will be your combined file)
-deselect enable load for all of the metro queriesclose the power query editor and have fun building your visuals in Power BI.
Community SupportHi HCC ,
I download your attached Excel, it's in the below format:
What I'm not clear is how did your get the below expected result, what's the relationship between the two snapshots.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
The first snapshot is the data for Hilton Head (which you seen the second snapshot on the 10th row). The second snapshot is an excel formula (a vlookup) that organizes the data from the first snapshot into a row.
My goal is to capture all the different metros, like the one in the link, and organize it into a nice neat data table as shown in the second snapshot using power BI. Let me know if this clears things up!
Thank you for your response!
