Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a text file that has text separated by different tab lengths (Refer to the example below). I want to clean up the data by separating each grouped string into their own columns. In the example, the tab length between A and A Description, A Description and A Category is the same for all rows (A1-A6, B1). Some rows have an additional field between the Description and Category, also with consistent tab size on both sides between the Description and Category fields.
What I've tried to do:
1. Create a Source query that imports the text file. Reference this query in the next step.
2. Used the Split Column by Delimiter Function, copying the tab size between each field in the preview into the custom delimiter. Function below:
=Table.SplitColumn(Source, Column 1, Splitter.SplitTextByEachDelimiter({"#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)"},QuoteStyle.Csv,false){"Column1.1","Column1.2"})
3. Repeated Step 2 with different tab sizes to split remaining fields.
Question: Is there a more intuitive way of doing this? Is this the recommended route (to delimit by tab size)? It assumes the tab sizes between fields are the same in the source text file but I'd like to be able to future proof it in the case a text file has different tab sizes to the original text file used to develop the query.
Example (with generic data):
Tranform into:
Hi @RokuCap - it would be nice to have a sample file to play with. Could you upload one?
Otherwise this is what I am thinking. I would keep the rows in 1 column. I would apply a Trim function to align the A and B to left. I might try the following function, Splitter.SplitTextByCharacterTransition - PowerQuery M | Microsoft Docs. The idea here is to find the transition from Character to Tab. Another Option it to split for column1 and remainder, then split column2 and remainder, and column 3 and remainder etc... Another option is to split the rows into group A and group B, then split each group according then Append (Table.Combine) back together again.
I hope this helps you think of some different approaches.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.