Excel Magic Trick 1475: Power Query, Formula, Flash Fill: '1-PSD/AFX-12' into '12-AFX/PSD-1'

In this video learn how to rearrange a product code using Excel Spreadsheet Functions and Power Query Functions. Also see that Flash Fill does not seem to work. Learn the Power Query equivalent for LEFT, RIGHT and MID Functions.
Topics:
Topics:
1. (00:11) Introduction
2. (01:36) See an example of Flash Fill and how it does not seem to work
3. (03:04) See Excel Spreadsheet Formula that uses the Functions MID, RIGHT and LEFT.
4. (06:07) See Power Query (M Code Functions) Custom Column Formula that uses the Functions Text.End, Text.Middle and Text.Start.
5. (10:33) Summary

[eddieturner]

All items have to be reversed 1-PSD/AFX-12 has to be changed to 12-XFA/DSP-1 which works. It gives a good challenge for the Microsoft programmers to figure out. Thanks for the fun . Love your Excelsfun site.

[entertainmentgalaxy]

beautiful formula. mixture of right, mid and left function. I am blessed to have excelsfun channel and a person who owns it.
Thanks for sharing such a beautiful video.

[SabreDog]

Another excellent video. Been using Excel for quite a few years and love learning new tips and tricks.  For example, double click the format painter and it stays active until you click it again.  Little things like this can save a lot of time!  Thanks!

[officetrain]

Good to see the Power Query text functions in operation. A dumber way of doing this in Power Query is to use the Split Column button on the Home tab to split the references first by the - and then by /. Then use Column from Examples on the Add Column tab and type the first finished code you require. This seemed to generate the correct product codes for me.

[BoBtheMule]

FYI: I was able to handle this in PQ/G&T much easier by using Columns by Example. Two entries and it completed it all for me!

[oliverf.]

Hi, thanks for all of your awsome videos!
Regarding Flash Fill:
when you fill the first three chells (B7/B8/B9) manually, Excel will understand the pattern. It Flash Filled all the other cells below correctly than.
Enjoy 2018!
Best, Oliver

[MrDoubleYa]

Great video and love learning the various functions of Power Query.
FYI Power Query can also do this using the function but the single line formula above is obviously less code and probably runs faster. Found this in a book by Gil Raviv, "Collect, Combine, and Transform Data using Power Query in Excel and Power BI". Works great when you need to split a column by multiple delimiters.


let
Source = Excel.CurrentWorkbook(){[Name="StartCode"]}[Content],
#"Split Col by Mult Dels" = Table.SplitColumn(Source, "Code", Splitter.SplitTextByAnyDelimiter({"-", "/"}, QuoteStyle.Csv, false)),
#"Added Custom" = Table.AddColumn(#"Split Col by Mult Dels", "Swap PQ", each [Code.4]&"-"&[Code.3]&"/"&[Code.2]&"-"&[Code.1]),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom", {"Swap PQ"})
in
#"Removed Other Columns"

[PaKumarSistla]

Awesome Mike...Thank you very much and HappyNewYear :)

[mohamedchakroun]

Pretty lovely this tricks with powerquery lot of fun

[alexj]

The PQ approach works well when the data is clean. Engineering data I usually receive has no guarantee of consistency, so I would use PQ to split and reassemble the segments, and not depend on the character length.

[MatthewJMcCarthy]

Thanks very much, that's really well done and helpful as well!

[MrDoubleYa]

In Power Query if you don't want to use the formulas you can split the column one parameter at a time (starting from the left "-", "/", "-"). This should give you 4 columns. Then you can create a new column by concatenating the 4 split columns in the required order just like you could in Excel. Hope that helps.

[davidgilbert]

for flash fill you can get it to see those characters before the slash by putting the slash in quotes to do the fill and then do a relace to remove the quotes

[NoShadowOfDoubt]

I'm addected to power query! Thank you

[mehdihammadi]

Very interesting thank you for sharing

[DaveH]

Excellent as always Mike, incidentally the flash-fill method can work if you use a helper column. I get that it's not perfect, but as a one-time deal on a large data set... maybe :)

[michaelalde]

Hi Mike; first: thx for sharing all ur skills with us. I tried many Files including this one. As u said/demonstrated here Flash Fill doesnt work in Excel. So i went in PQ and uses "Column From Examples(what in fact is Flash Fill Function - also in Power B available). After I entered the Pattern in 2 rows. it follows then the PAttern perfectly- Lols- magic

[DougHExcel]

Too bad flash fill didn't work out, thanks for reminding about the Ctrl+E shortcut!

[vida]

It was interesting to learn PQ formulas. Shame that Microsoft has different names for the same functions.

[officetrain]

With Excel 2016 Standalone, I had the same problem with FlashFill. I think the repeated use of the dash is confusing it. I was able to get flash fill to work using the re-arranged code: 12$AFX/PSD-1. Then I selected the FlashFill results and used ReplaceAll to change $ to -. Using Replace to do them one at a time triggered FlashFill to give the faulty codes again.