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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

AugPowerBI_Carousel

Power BI Monthly Update - August 2024

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

August Carousel

Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors