Forum Discussion
Responsive ResidentPower 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
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
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 MagicMagicedited step "SplitColumns"
4 Replies
- ImkeF
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"- 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
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 MagicMagicedited step "SplitColumns"
