Cleaning Data

[tomshaw83]

Hi,

I am looking to reshape some messy data from a PDF table as in the example below, to create the table below that.

I can perform this transition (i think) with many, many, many applied steps. Though if there are specific techniques that produce a more efficient solution I would be keen to see them, or if anyone knows of any resources online that I can research into this I would be keen to upskill.

	Header 1	Header 2		Header 3
	Fact 1 Value 1	Fact 2 Value 1		Fact 3 Value 1
Header 4	Fact 4 Value 1		Header 5	Fact 5 Value 1	Header 6	Fact 6 Value 1	Header 7	Fact 7 Value 1

Header 1	Header 2	Header 3	Header 4	Header 5	Header 6	Header 7
Fact 1 Value	Fact 2 Value	Fact 3 Value	Fact 4 Value	Fact 5 Value	Fact 6 Value	Fact 7 Value

Thank you

Tom

[tomshaw83]

Thank you, some useful techniques in your solution

[v-jingzhang · Accepted solution]

Hi @tomshaw83 

I'm not sure if my solution is more efficient than yours. But my solution is all processed with the user interface without needing to modify the M codes in Advanced editor. You can download the attached pbix file to check specific steps.

To make it clearer, I deal with the data in separate queries and combine them at the end. 

Hope this helps.

Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.