cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
leroy773
Helper II
Helper II

Would like convert excel table with date headers as columns to a data table for power bi

Currently, I have an export of data to excel.  It has a column for customers, serial numbers, and dates by month.  In the date columns it shows the consumption per month.  I would like to convert this to a data table that would allow me to report in power bi.  Below is the original format, and the following table what I would like to convert it to, looking for an efficient method to convert

 

CustomerSerial # Jan 2021Feb 2021Mar 2021Apr 2021May 2021Jun 2021Jul 2021Aug 2021
A1         
A2         
C3  1423376
D4         
E5         

 

 

CustomerSerial # DateUsage
A1 Jan 2021 
A1 Feb 2021 
A1 Mar 2021 
A1 Apr 2021 
A2 Jan 2021 
A2 Feb 2021 
A2 Mar 2021 
A2 Apr 2021 
C3 Jan 2021 
C3 Feb 20211
C3 Mar 20214
C3 Apr 20212
1 ACCEPTED SOLUTION
speedramps
Super User
Super User

Hi Leroy !

 

Thank you for describing the problem with an example input data table (not a screen shot)

and a very cleary example of what output is required. Well done!!! I wish everyone did that

Some other members on this forum write awful descriptions.
It make it a joy to help you.

Please quote @speedramps if you have anymore problems if you always describe them this well, and I will receive an automated notification.

 

Try this ...

Get the Excel raw data and

In Power Query editor hold the <CTRL> key to select both the Customer and Serial # columns.

The right click the mouse and Unpivot other columns

See attached screen pints

You may then want to rename the column and change the data types

 

 

Thanks for reaching out for help.

I have helped you, now please help me by giving kudos.

Remeber we are unpaid volunteers.

Click the thumbs up and accept as solution button. 

One question per ticket please. If you need to extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you !

 

speedramps_0-1656706887871.png

 

speedramps_1-1656706929800.png

 

View solution in original post

3 REPLIES 3
leroy773
Helper II
Helper II

Thanks for the quick reply.  That definitely obtained the output I was hoping for, had to add the step of replacing null values with 0, so months were not skipped if null value existed.   

Thanks Leroy.

Please click the Accept as Solution button because it help ranks my skills at solving problems.

speedramps
Super User
Super User

Hi Leroy !

 

Thank you for describing the problem with an example input data table (not a screen shot)

and a very cleary example of what output is required. Well done!!! I wish everyone did that

Some other members on this forum write awful descriptions.
It make it a joy to help you.

Please quote @speedramps if you have anymore problems if you always describe them this well, and I will receive an automated notification.

 

Try this ...

Get the Excel raw data and

In Power Query editor hold the <CTRL> key to select both the Customer and Serial # columns.

The right click the mouse and Unpivot other columns

See attached screen pints

You may then want to rename the column and change the data types

 

 

Thanks for reaching out for help.

I have helped you, now please help me by giving kudos.

Remeber we are unpaid volunteers.

Click the thumbs up and accept as solution button. 

One question per ticket please. If you need to extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you !

 

speedramps_0-1656706887871.png

 

speedramps_1-1656706929800.png

 

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors