Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started


How to consolidate interspersed rows

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!

Super User
Super User

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


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}})
#"Grouped Rows"

Helpful resources

Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.


Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors