Log In
Register
Home
Power Query
Power Pivot
Power BI
October 17, 2018
Offline
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
See Answer
July 16, 2010
Offline
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
Answers Post
1 Guest(s)
