Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
HCC
Frequent Visitor

Difficulty transforming data on PowerQuery

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:

HCC_0-1669843641090.png

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:

HCC_0-1669843872784.png

 

 

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

 

Grateful for your feedback!!!

1 ACCEPTED SOLUTION
jgeddes
Super User
Super User

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)

jgeddes_0-1669919089070.png

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

 

 




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





View solution in original post

4 REPLIES 4
jgeddes
Super User
Super User

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)

jgeddes_0-1669919089070.png

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

 

 




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





HCC
Frequent Visitor

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

 

v-yanjiang-msft
Community Support
Community Support

Hi @HCC ,

I download your attached Excel, it's in the below format:

vkalyjmsft_0-1669884878561.png

What I'm not clear is how did your get the below expected result, what's the relationship between the two snapshots.

vkalyjmsft_1-1669885011795.png

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!

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors