Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to 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
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
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.
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
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.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
109 | |
97 | |
95 | |
38 | |
36 |
User | Count |
---|---|
151 | |
125 | |
75 | |
74 | |
53 |