Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
Thanks in advance for any help.
I am bringing data into power bi from excel. This is an export from a third party so i dont have any control over the source format.
The data has some person information, then activitiy codes for AM and PM each day, for the last 2 working weeks. Example data below:
08-03-2023 | 09-03-2023 | 10-03-2023 | ||||||
ID | Forename | Surname | AM | PM | AM | PM | AM | PM |
1234 | Joe | Bloggs | . | . | / | \ | O | O |
Within power bi power query I need to format the columns so that it ends up like the following:
ID | Forename | Surname | 08-03-2023 AM | 08-03-2023 PM | 09-03-2023 AM | 09-03-2023 PM | 10-03-2023 AM | 10-03-2023 PM |
1234 | Joe | Bloggs | . | . | / | \ | O | O |
Alternatively, the following format would also work, and mayybe even better:
ID | Forename | Surname | Date | AM PM | Code |
1234 | Joe | Bloggs | 10/03/2023 | AM | O |
1234 | Joe | Bloggs | 10/03/2023 | PM | O |
1234 | Joe | Bloggs | 09/03/2023 | AM | / |
1234 | Joe | Bloggs | 09/03/2023 | PM | \ |
1234 | Joe | Bloggs | 08/03/2023 | AM | . |
1234 | Joe | Bloggs | 08/03/2023 | PM | . |
I need to take into account that this export is taken weekly, and each time the dates are different, so I need to add AM and PM as suffixes to column headers which change.
Its also complicated by the fact the person information are on the same row as the AM and PM, not the dates.
I am happy to include the example source spreadsheet and desired outcome, and also a .pbix with the source already loaded, but it looks like uploading those files is not supported here. Please let me know how I should do this.
Any help very much appreciated.
Solved! Go to Solution.
Have a look at the attached PBIX as a starting point.
It uses a function from Bill Szysz found on this blog post (https://www.thebiccountant.com/2017/06/19/unpivot-by-number-of-columns-and-rows-in-powerbi-and-power... ) by @ImkeF.
Let me know if you need further help/explanation.
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
Have a look at the attached PBIX as a starting point.
It uses a function from Bill Szysz found on this blog post (https://www.thebiccountant.com/2017/06/19/unpivot-by-number-of-columns-and-rows-in-powerbi-and-power... ) by @ImkeF.
Let me know if you need further help/explanation.
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
This is great. Much appreciated!