cancel
Showing results for 
Search instead for 
Did you mean: 
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
Solution Sage
Solution Sage

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.

 

 

View solution in original post

4 REPLIES 4
jgeddes
Solution Sage
Solution Sage

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.

 

 

HCC
Frequent Visitor

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

 

v-kalyj-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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors