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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
elliotdixon
Responsive Resident
Responsive Resident

Power Query - import txt file with multiple lines per row - change to one row

Hi All,

I am trying to import a txt file into Power Query however the length of the txt data is increasing and has spilled over to take up more than one line. I need to get multiple lines on the txt file to one row.

 

There are identifier values that I can use to determine where to make the next row start and end.

 Line begins with <Start> and line ends with </Start>

 

Any ideas on how I can get the lines to all merge on to one row

 

2016-04-11 12_12_57-57WashLogbackup - Query Editor.png

 

 

Code for one entry that I want all on one row.

<Start>
<Site>57</Site><Customer>EMERSONS BREWERY</Customer><WashNumber>57/41</WashNumber><Driver>CHRIS</Driver><Date>16/02/2016</Date><Des>Dynes T&T</Des><Reg>CGY612</Reg><Wash>DYNES TRUCK AND TRAILER</Wash><TargetBar>2.20</TargetBar><TargetLts>0</TargetLts>
<StartTime>18:45:44</StartTime><EndTime>18:48:59</EndTime>
<Temp>65</Temp><Conduct>0.07</Conduct><ActLts>311</ActLts><ActBar>1.26</ActBar><Side>PASS</Side><Kwh> 0.2</Kwh><WaterUsed> 311</WaterUsed><MaxFlow>1331.38</MaxFlow><MaxBar> 0.00</MaxBar><AVGFlow>1036.00</AVGFlow><DELAFACTOR> 0.00</DELAFACTOR>
<DELAHPFACTOR> 0.00</DELAHPFACTOR><WaterMeter>0</WaterMeter><EleMeter>  35</EleMeter><DelATime>00:48:49</DelATime><DelATimeHP>00:00:00</DelATimeHP><ReturnATime>00:58:30</ReturnATime>
<DelBTime>00:00:00</DelBTime><ReturnBTime>00:00:00</ReturnBTime><HotWater>20:04:35</HotWater>
<Dosing>00:02:19</Dosing><Boiler>00:43:28</Boiler><WaterMeter> 15.498</WaterMeter><HotWaterMeter>  0.733</HotWaterMeter><FinalWaterMeter>  4.561</FinalWaterMeter><PreWaterMeter>  2.500</PreWaterMeter><ElectricMeter>    306</ElectricMeter>
</Start>

Cheers,

ED

 

1 ACCEPTED SOLUTION

So we need to apply some more M(agic) here:

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    IndexFirst = Table.AddColumn(#"Added Index", "Custom", each if [Column1]="<Start>" then [Index] else null),
    #"Filled Down" = Table.FillDown(IndexFirst,{"Custom"}),
    GroupAndCombine = Table.Group(#"Filled Down", {"Custom"}, {{"Count", each Text.Combine(_[Column1]), type table}}),
    SplitAgain = Table.AddColumn(GroupAndCombine, "Custom.1", each Text.Split([Count], "><")),
    Show = Table.ExpandListColumn(SplitAgain, "Custom.1"),
    SplitColumns = Table.AddColumn(Show, "NewCols", each List.FirstN(Text.SplitAny([Custom.1], "<,>"),2)),
    Magic = Table.Group(SplitColumns, {"Custom"}, {{"Magic", each Table.PromoteHeaders(Table.FromColumns(_[NewCols])), type table}}),
    MagicMagic = Table.Combine(Magic[Magic])
in
    MagicMagic

edited step "SplitColumns"

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

4 REPLIES 4
ImkeF
Community Champion
Community Champion

Try this code:

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [Column1]="<Start>" then [Index] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Custom"}, {{"Count", each Text.Combine(_[Column1]), type table}})
in
    #"Grouped Rows"

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

elliotdixon
Responsive Resident
Responsive Resident

Hi @ImkeF Thanks! Thats great. It does exactly what I need.

 

Only thing is that I can't now seem to split the resulting table that is created in PowerQuery.

 

2016-04-12 14_18_07-Query1 - Query Editor.png

 

How do i expand in Power Query to start modelling the data into the correct columns?

Cheers.

So we need to apply some more M(agic) here:

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    IndexFirst = Table.AddColumn(#"Added Index", "Custom", each if [Column1]="<Start>" then [Index] else null),
    #"Filled Down" = Table.FillDown(IndexFirst,{"Custom"}),
    GroupAndCombine = Table.Group(#"Filled Down", {"Custom"}, {{"Count", each Text.Combine(_[Column1]), type table}}),
    SplitAgain = Table.AddColumn(GroupAndCombine, "Custom.1", each Text.Split([Count], "><")),
    Show = Table.ExpandListColumn(SplitAgain, "Custom.1"),
    SplitColumns = Table.AddColumn(Show, "NewCols", each List.FirstN(Text.SplitAny([Custom.1], "<,>"),2)),
    Magic = Table.Group(SplitColumns, {"Custom"}, {{"Magic", each Table.PromoteHeaders(Table.FromColumns(_[NewCols])), type table}}),
    MagicMagic = Table.Combine(Magic[Magic])
in
    MagicMagic

edited step "SplitColumns"

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

elliotdixon
Responsive Resident
Responsive Resident

Wow @ImkeF Thanks heaps. You completly solved my whole process!
I really need to learn more about M language! So powerful when used by the right hands.

 

Amazing help. Cheers.

ED.

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

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

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.