Skip to main content
cancel
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

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
Super User
Super User

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
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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