Power Query: Excellent Examples
5 September 2018
Welcome to our Power Query blog. July brought some improvements to Power Query/Get and Transform. I look at how creating a column from examples has developed.
As regular readers will know, ‘Column from Examples’ is a favourite of mine, so when I saw that it had been improved again, I wanted to see what I could do with it now. The good news is that I can now multitask by using this feature, as I can use it to combine transformations.
This reminded me of a problem I was trying to solve in Power Query: Initial Problems. In that blog, I wanted to reformat some names:
My aim was to transform the FULL_NAME so that I could have ‘C. Parr’ instead of ‘CHRISTINE PARR’. The problem I came up against (shown in version 2013 of Power Query) was that ‘Column from Examples’ couldn’t cope:
So, I am going to try that again!
In the ‘Add Column’ tab there is a section called ‘Column from Examples’ and I am going to allow Power Query to get data for my new Column ‘From All Columns’.
Well this is definitely an improvement! After two examples, my new column has been created. I click ‘OK’ to get my new column:
= Text.Combine({Text.Start([FULL_NAME], 1), ". ", Text.Proper(Splitter.SplitTextByDelimiter(" ", QuoteStyle.None)([FULL_NAME]){1}?)})
I delete this column and try combining data from other columns – this time, not only do I want to format the name, I also want to include the job title and region:
I create the column to see the full transformation.
= Text.Combine({Text.Start([FULL_NAME], 1), ". ", Text.Proper(Splitter.SplitTextByDelimiter(" ", QuoteStyle.None)([FULL_NAME]){1}?), " (", [REP_TYPE_C3], ") ", [AREA_CODE]})
It shows that it’s worth trying out new methods to solve old problems, as Power Query is constantly improving.
There are also developments in using dates in ‘Column from Examples’ for date formats used in specific domains though I found this functionality a bit hit and miss, hopefully it will improve. I tried it with JOIN_DATE in the following example:
The date is translated to MM-YY from a full datetime format.
The M formula is
= Text.Combine({DateTime.ToText([JOIN_DATE], "MM"), "-", DateTime.ToText([JOIN_DATE], "yy")})
I can also extract the name of the month:
I create the new column.
The M formula used is:
= Text.Combine({DateTime.ToText([JOIN_DATE], "MMMM"), " ", DateTime.ToText([JOIN_DATE], "yyyy")})
It’s good to see the functionality is expanding, making ‘Column from Example’ an even more useful feature – especially for beginners.
Come back next time for more ways to use Power Query!
