Request with M code

Power Query M Code Showcase
1

Hello,

Please can you help with my problem.
I have this data as shown below;

image
image787Ă—830 17.8 KB

The text coloured in green is supposed to be the Headers for the columns while the yellow and pink colours would be row1, row2, row3, row4 and row5

The expected output would look like;

image
image1900Ă—182 22.5 KB

I would be attaching the file that contains the problem and the expected output.
Please note that I would be attaching an excel file but I need my result in pbi file.

Many thanks
Iwona
Problem Doc.xlsx (17.5 KB)

2

@Iwona Here is a solution but make sure that any row in the source table is not Null. The word Null is fine but the cell shouldn’t be empty. Also, there should be same number of entries for each person i.e. 14(number can change but it should be same for everyone) Because the solution is based on a pattern.

Problem Doc.xlsx (31.3 KB)

Iwona.pbix (27.9 KB)

3

Thanks @AntrikshSharma .

I will give it a go and update you on the result.

XX
Iwona

4

Hello @AntrikshSharma,

Thanks for your effort but this did not work on my real data as my real data is in xml format.
Please, can you explain to me how to implement the code using xml file as my data source.
Sorry for not making this clear at first as I was trying to Mock up the problem with the data in excel.

XX
Iwona

5

Hi AntrikshSharma,
I am submitting a possible solution based on Brian Julius’s video “How To Handle Stacked Data In Power BI”.
I am learning Power BI and I would like to know your opinion about the solution that I have created.
Thanks a lot.
Iwona_JAFP.pbix (36.7 KB)

6 Likes
6

Hi Iwona,
Can you share the file in xml format?
Thank you.

7

@jafernandezpuga Great solution! Thanks for participating, this is the best way of learning any skill!

@BrianJ would be happy to see that his recent video helped you create this solution :slight_smile:

5 Likes
8

Thank you for sharing your solution @jafernandezpuga

Amazing job on how you utilized @Brianj’s video to provide a solution.

Hi @Iwona, did the solution provided above helped you with your inquiry?

1 Like
9

Hi,

Thanks @EnterpriseDNA for asking but the solution didn’t work for me as my data is in xml. @jafernandezpuga asked if I could share the xml data but I couldn’t because I am not sure how to mask the data as it’s our company data.

I copied @jafernandezpuga code and changed the source to my xml file location but the rows were miss-matched from column to column which was not useful.

If anyone could give me a clue on how to mask the xml data, that would be helpful.

Thanks
Iwona

10

Hello @Iwona, here are some videos that may help you masking sensitive data and create datasets and data models representative of your problem:

2 Likes
12

Hi @Iwona, we’ve noticed that no response has been received from you since a few days ago.

We just want to check if you still need further help with this post?

In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.