Difficulty transforming data on PowerQuery

[HCC]

Hello all!

I am a consultant that utilizes commercial real estate data across the U.S. I am very proficient in Excel, but would like to start using Power BI because it is easy to share and generate reports.

My current issue is transforming data I have queried from the web (Bureau of labor statistics) and transforming it into the format that works best. The current format is in a table with the columns separated by month (ex. Jan, Feb, Mar, etc.) and the rows by year (2012, 2013, ..., 2022). Screenshot below:

Sample link: https://data.bls.gov/timeseries/SMU45259400000000001?amp%253bdata_tool=XGtable&output_view=data&incl...

I wrote a simple vlookup formula in excel that was able to organize it into a line format for the entire table. 

Here is an example of how I organized it on Excel:

My question is, how can I do that on Power BI?

Grateful for your feedback!!!

[jgeddes · Accepted solution]

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 queries

close the power query editor and have fun building your visuals in Power BI.

[HCC]

I can't thank you enough!!! I appreciate the help!

[v-yanjiang-msft]

Hi @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.

[HCC]

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!