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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Syndicate_Admin
Administrator
Administrator

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!

1 REPLY 1
amitchandak
Super User
Super User

@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"

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.