How to transform text file with a header line and detail line to only one line

[Danielecc]

Hello everyone, 

I have a lot of text files to put in a "Folder" for get data, but the original format is like this:

But I need transform that format like this:

This is part of an original file in texto mode (if start whit "1" is header and next line with "2" is the detail:

1 12345678-1 Juan Perez 1 3210138008 T 121595
2 30.08.2021 32510 32510 AB
2 30.08.2021 89085 89085 AB
1 98765432-4 Carlos Gonzalez 016 70603057 T 64798
2 30.08.2021 64798 64798 AB
1 56789123-0 Pablo Diaz 12 10903401 T 584260
2 30.08.2021 584260 AB
2 30.08.2021 706540 254223 AB
2 30.08.2021 102295 AB

Thanks a lot for your help and sorry by my english 🙂

Best regards...

[v-yanjiang-msft · Accepted solution]

Hi @Danielecc ,

According to your description, I create a sample. Here's my solution.

1. In your snapshot, the contents of 1 and 2 are almost not in the same column except for the column in the red line in the image below.

 

So first create a new custom column to seperate them.

Custom = if [Column1] = 1 then [Column2] else null

Result:

2.In the text columns, replace the blank value with "null".

3.Select all columns belonging to 1, then right-click to select Fill Down.

4.Then filter out 2, and then delete the unneeded columns, reorder other columns, get the result.

Here's the applied steps code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jVBBisMwDPxK8LkpI9my5WO3hYU99bC30IMXelgIDaTspa9vUjvZFAItGCR5NGhmmsaQ2Rhi68QHrcdheF9/6VIdz/35lufybZlAVgHN83dBmSSKOW0aw+MWttAtg+lBEcLEntvdR67/b5WsESp5YdGuk8f9qMGLs1y7DOxT33bX6rO73FI7eQH53AR4WEh48uJdiLquJkOLrRdqxkDjkGxdTB/TT9tVh980hcqlIsI60JMOUcce60IKtrj5Zp6DY3GY3c/9m3QCc5QXd093", 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, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t, Column12 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", type text}, {"Column3", Int64.Type}, {"Column4", type text}, {"Column5", Int64.Type}, {"Column6", Int64.Type}, {"Column7", type text}, {"Column8", Int64.Type}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Column1] = 1 then [Column2] else null),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom","",null,Replacer.ReplaceValue,{"Column4", "Column10"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Column4", "Column6", "Column8", "Column10", "Column12", "Custom"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Column1] = 2)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Column1", "Column9", "Column11"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Custom", "Column4", "Column6", "Column8", "Column10", "Column12", "Column2", "Column3", "Column5", "Column7"})
in
    #"Reordered Columns"

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Danielecc]

@v-yanjiang-msft , I saw your pbix file and that's is what I need.

I will apply in my pbix file to see the result and then I will "Accept as Solution".

Thanks a lot and best regards...