How to treat table with stacked data

[AndreWahlbuhl]

Hello fellows!  I know you are advanced guys and I have a simple user question that is making me crazy.

I have tons of files in CSV to be treated on PowerQuery. The problem that the data on that files is stacked. I dont know how to treat them on Power Query. I dont know how to separate the data in lines to rows having in mind that a single line corresponds a 4 lines of date on the original table. 

I´m attaching a PDF extraction of that table. 

I dont use Excel to treat them because the idea is to make it an process, reading such a dump folder and creating such a incremental dashboard.

Thanks a lot for your help.

 

[Jimmy801 · Accepted solution]

Hello @AndreWahlbuhl 

I don't know how the document header is structured and if you need this information somehow too. I assume that you have anonymous column header and then stacked information whereas one row is structured in 3 rows like this. In case you have a document header, you would need somehow to eliminate your header (by deleteing the first rows or by checking one of your column until a certain value is reached - maybe "Name" in column1

In this case you can split the table with pagesize 3 and then transform the list of tables by referencing the column and the row. After that the records can be transformed to a table. Would you need also something of your document header, you could now add an additional column where you referencing your first step.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY+7CsMwDEV/RXgOwXGW0rnN2KFJJ5PBUEEMrh0kJdC/b+I+SDsVNIhzH0jWqpO7oSrUZ1pxMnFecGTVF3ZFJHhdWOOj5yGv5ylC53P2gGNId7gw0qtmTR2JEm2b38Lq09UWN86HXJp1U5eVKY02GvRub8zC/0MdsrRI89cZz26QBIScwowwJBaIP29ne/8A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t]),
    SplitTable = Table.Split(Source,3),
    TransformListsOfTables = List.Transform
    (
        SplitTable,
        each [1= [Column1]{0}, 2= [Column5]{0}, 3= [Column6]{0}, 4= [Column1]{1}, 5= [Column2]{1}, 6= [Column3]{1}, 7= [Column4]{1}, 8= [Column1]{1}]
    ), 
    CreateTableFromRecords= Table.PromoteHeaders(Table.FromRecords(TransformListsOfTables))
in
    CreateTableFromRecords

this is the outcome

Copy paste this code to the advanced editor in a new blank query to see how the solution works. 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[AlB]

Hi @AndreWahlbuhl 

Can you:

1. Share a couple of the files you need to process

2. Very importantly: for one for those files, show the expected result after processing in PQ

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AndreWahlbuhl]

Sorry, I could´t post anything mothe than that. It is property of the company. 

but Im very thankful you helped me a lot guys!