Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Customer | Serial # | Jan 2021 | Feb 2021 | Mar 2021 | Apr 2021 | May 2021 | Jun 2021 | Jul 2021 | Aug 2021 | |
A | 1 | |||||||||
A | 2 | |||||||||
C | 3 | 1 | 4 | 2 | 3 | 3 | 7 | 6 | ||
D | 4 | |||||||||
E | 5 |
Customer | Serial # | Date | Usage | |
A | 1 | Jan 2021 | ||
A | 1 | Feb 2021 | ||
A | 1 | Mar 2021 | ||
A | 1 | Apr 2021 | ||
A | 2 | Jan 2021 | ||
A | 2 | Feb 2021 | ||
A | 2 | Mar 2021 | ||
A | 2 | Apr 2021 | ||
C | 3 | Jan 2021 | ||
C | 3 | Feb 2021 | 1 | |
C | 3 | Mar 2021 | 4 | |
C | 3 | Apr 2021 | 2 |
Solved! Go to Solution.
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 !
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.
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 !
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |