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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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