I have been dipping my toe into Power Query, and it has really been eye opening on time savings and what you can do.
Today, however it failed to do what I thought it should do. I'm sure it is because I have limited knowledge, but I was hoping someone could explain why the query didn't work the way I wanted.
I duplicate a column and try to split it by delimiter colon, but the query doesn't recognize the colon in some instances. When I load the query to excel, and I try a text to column function in excel based on colons, excel is able to split the column the way I anticipated by colons. The attachment with columns highlighted in yellow are the Text to columns function applied.
Is this a limitation in Power Query, or is my understanding of this process faulty? Anything you can share to help me understand what is happening in splitting columns this way is very much appreciated.
The file format comes in from our system as MS Excel 97-2000, but I'm using Excel for office 365
Hi Steve,
Great to see you're giving Power Query a go.
There is something quirky with the colons in that data. If you change the Quote Style to QuoteStyle.None in the Split Column by Delimited step it will correctly detect the colons. This is what you see in the formula bar and the code in red is what you need to change:
= Table.SplitColumn(#"Duplicated Column", "Column2 - Copy", Splitter.SplitTextByDelimiter(":", QuoteStyle.None), {"Column2 - Copy.1", "Column2 - Copy.2", "Column2 - Copy.3"})
Mynda
Thank you so much for your help. You are very generous with your time and knowledge.
