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

[elliotdixon]

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

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

[ImkeF]

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"

[elliotdixon]

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.

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

Cheers.

[ImkeF · 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"

[elliotdixon]

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.