Forum Discussion

tomshaw83's avatar
tomshaw83
Helper I
5 years ago
Solved

Cleaning Data

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 1Fact 2 Value 1 Fact 3 Value 1     
Header 4Fact 4 Value 1 Header 5Fact 5 Value 1Header 6 Fact 6 Value 1Header 7Fact 7 Value 1 

 

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

 

 

Thank you


Tom

 

 

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    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.

2 Replies

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    5 years ago

    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.