transform non structured row value with m script

[katelinf]

Hello,

I have non structured data like this:

Sector & Company |	Sales Amount
Sector A	null
Company 1	100
Company 2	200
Sector B	null
Company 3	200
Company 4	150
Company 5	300

How can I convert this data to this structure with m script?

Sector |	Company |	Sales Amount
Sector A |	Company 1 |	100
Sector A |	Company 2 |	200
Sector B |	Company 3 |	200
Sector B |	Company 4 |	150
Sector B |	Company 5 |	300

[SpartaBI · Accepted solution]

@katelinf Here is a link to download the file with the solution:
transform non structured row value with m script 2022-07-28.pbix

      

Showcase Report – Contoso By SpartaBI