Power Query - Help with column format

[Evans2]

Hi all,

I'm new to Power Query but I'm already really impressed by what it can do and I'm very excited about the possibilities.

I'm pulling data directly from an oracle server into Power Query and noticed that some of the columns have a strange format that I will need to change but I'm unsure how.

Some of the date fields are formatted as =T(""01-MAY-2013"") but I need the format to be 01-MAY-2013. Another is formatted as =T(""P-5201-A"") but I need the format to be P-5201-A.

Any help is greatly appreciated.

Evan

 

[ImkeF]

Hi Evan,
welcome to the fan-club Power Query is fantastic – one can only love it!

But you actually picked a surprisingly weak point: PQ doesn’t have Regular Expressions capabilities nor (at least to my knowledge) does it support removal of strings. You can remove characters or a list of single characters, but in your case – if we would remove the Ts, OCT-values would be corrupted. It would be very nice if we could just remove these strings: =T(“” and “”) or define a regex like “keep TT-MMM-YYYY”. But no such thing to my knowledge now. So please vote for this feature here:

https://support.powerbi.com/forums/...egular-expression-support-to-as-native-databa

So for now we have to improvise:
if Text.StartsWith([Date],"=T(""") then Text.Range([Date],5,11) else [Date])

This checks the beginning of your string and returns a string with a defined length starting at the defined position.

If your “strings to keep” are not of the same length, then you could split your column with “ as delimiters. This is best done using the UI: Transform – Text Column – Split Column – By Delimiter – Custom …
This will separate the date expression in a separate column.

hth, Imke

 

[Evans2]

Thank you very much for your reply. I think it will work best for me to delimit as some of the strings are different lengths.

The data would pull into a power pivot spreadsheet used by many users and would need to be refreshed often. Could you tell me if new data pulled from the oracle server would also be in the same delimited format?

Thanks again!

 

[ImkeF]

Yes, that's one of the fundamental ideas behind Power Query.
They will all appear in the same separated column as long as the characters to cut-off share the same pattern.

 

[Evans2]

So great. This will solve the problem.

I also voted to add regular expression support for future versions.

Thanks

 

[Evans2]

What I just found out (probably should have asked sooner) was that if I delimit columns in Power Query, this will carry over to the data source (oracle server).

This is causing a lot of problems now as we're unable to run other reports. I wasn't able revert back to the previous format.

Do you know if there is a way to pull data so that it is read only so that any formatting done in Power Query does not carry over to the data source?

 

[billszysz]

But this is not because of PQ. PQ doesn't change the data source.

 

[Evans2]

That's exactly what I thought so I was happily reformatting columns not thinking it would change the source data in any way, but it did.

 

[Evans2]

Hi Imke,

Trying out your first solution but having some trouble with the DAX syntax. One of the date columns I need to change is called PERIOD_TO.

Here are my attempt - =IF([PERIOD_TO],"=T("""),([PERIOD_TO],5,11),[Date]) or =IF([PERIOD_TO],"=T("""),IF([PERIOD_TO],5,11),[Date])

Thank you for the help.

 

[Evans2]

Ugg sorry that is all wrong. I'll keep working at it.