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
Elscc
Frequent Visitor

Dynamic Column Headers from multiple rows

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 
IDForenameSurnameAMPMAMPMAMPM
1234JoeBloggs../\OO

 

Within power bi power query I need to format the columns so that it ends up like the following:

 

IDForenameSurname08-03-2023 AM08-03-2023 PM09-03-2023 AM09-03-2023 PM10-03-2023 AM10-03-2023 PM
1234JoeBloggs../\OO

 

Alternatively, the following format would also work, and mayybe even better:

 

IDForenameSurnameDateAM PMCode
1234JoeBloggs10/03/2023AMO
1234JoeBloggs10/03/2023PMO
1234JoeBloggs09/03/2023AM/
1234JoeBloggs09/03/2023PM\
1234JoeBloggs08/03/2023AM.
1234JoeBloggs08/03/2023PM.

 

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. 

1 ACCEPTED SOLUTION
KNP
Super User
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 ;).
chrome-9xf-Zagzel-B

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.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

View solution in original post

2 REPLIES 2
KNP
Super User
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 ;).
chrome-9xf-Zagzel-B

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.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x
Elscc
Frequent Visitor

This is great. Much appreciated!

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.

Top Solution Authors
Top Kudoed Authors