How to consolidate interspersed rows

[Syndicate_Admin]

Good afternoon.

I hope you are all well.

I have several txt in the following format

dato1 dato2 fecha1

dato3 dato4 fecha2

01 002 01/01/2022

0004 0051 23/02/2022

How can I load a table that fits the following format?

dato1 dato2 fecha1 dato3 dato4 fecha2

01 002 01/01/2022 0004 0051 23/02/2022

Thank you!

[amitchandak]

@Syndicate_Admin , Try this code in the power query. Create a new blank query open and paste it there

let
Source = Csv.Document(File.Contents("C:\Users\admin\Downloads\test.csv"),[Delimiter=",", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
#"Transposed Table" = Table.Transpose(#"Changed Type"),
#"Pivoted Column" = Table.Pivot(#"Transposed Table", List.Distinct(#"Transposed Table"[Column2]), "Column2", "Column3", List.Max),
#"Pivoted Column1" = Table.Pivot(#"Pivoted Column", List.Distinct(#"Pivoted Column"[Column1]), "Column1", "Column4", List.Max),
#"Grouped Rows" = Table.Group(#"Pivoted Column1", {}, {{"DATOS3", each List.Max([dato3]), type nullable text}, {"DATOS4", each List.Max([dato4]), type nullable text}, {"FECHA2", each List.Max([fecha2]), type nullable text}, {"DATOS1", each List.Max([dato1]), type nullable text}, {"DATOS2", each List.Max([dato2]), type nullable text}, {"FECHA1", each List.Max([fecha1]), type nullable text}})
in
#"Grouped Rows"