Power Query Transformation of Unstructured Data Table

[richy89]

Hi All,

I have attached a file showing a table with multiple headers and a format that is rather difficult for me to transform in Power Query to rows. Hoping someone here can assist.
I provided desired outcome as well.

Cheers

[sandy666]

maybe first fix source data: no merged cells (this is the greatest sin), no blank columns between data, no blank rows in the table
then you can try to transform

[richy89]

Unfortunately the data source is controlled by a third party and the data set is quite large. I have to come up with a solution without changing the table format.

[alansidman]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[richy89]

Unfortunately it doesn't. Hopefully some one helps

[alansidman]

@richy89
What does this mean if not solved

I have to come up with a solution without changing the table format.

[John Vergara]

Hi to all

Name the range as "Data" (without quotes), and check this option in Power Query:

Please Login or Register  to view this content.

Blessings!

[sandy666]

I have to come up with a solution without changing the table format.

this is not possible with Power Query because PQ will split merged cells which means that the format of the source table has already been changed in PQ Editor
or describe in detail and logically what you mean by writing the above

[josephteh]

Sandy, check out the M Code by John in post #7, it works perfectly without modifying the source data!

[josephteh]

Almost perfect.. except to remove the unnecessary comas in the System column.

[richy89]

Hi to all

Name the range as "Data" (without quotes), and check this option in Power Query:

Please Login or Register  to view this content.

Blessings!

This is perfect. many thanks!

[sandy666]

Almost perfect.. except to remove the unnecessary comas in the System column.

no extra commas and untouched original source table

Header	Title	Code	System	Location A	Location B	Area A Support	Area B Support
China	FIFA A	VKI	Socks,Ballards	Scott Willis	Jane Clint
China	FIFA B	XIR	Okra	John Smith	Shaelly Mark
New Zealand	Aruaz X	DKQ	Fence,Hyndee,Vxil,Sons,Geild	Chris Lee	Tan Boyk
New Zealand	Aruaz Z	PZV	Vresim,Charlo,Zim,Olasd	Veronica Elis	Pigus Jasd	Vacant	Ukloba Hesend

Please Login or Register  to view this content.

[josephteh]

Great job, Sandy! Rep for you and John!

[sandy666]

Thanks and Welcome

[richy89]

Hi John,

Thanks for all your help.

How can I further enhance it to recognize more merged cells like the ones highlighted yellow? See attached updated file.

Attachment 830617

Cheers

[John Vergara]

Hi again, richy89!

Power Query really not recognize the merged cell... just the first cell (left and up) of the merging area. The anothers merged cells was fine, because they have another cell that tell to Power Query where to stop (or data ends too). Blessings!

[richy89]

All good then.

Thanks again to you and everyone for resolving this!

[sandy666]

you are welcome