Power Query/BI Question - Merging Query Within Itself

[ecarroll]

Hi - I have a query that looks something like this:

Company Name	Type	Code
ABC Co	Initial
ABC Company	Final	1234.100
ABC FS	Change
XYZ Co	QC
XYZ Co	Final	5678.100
Example Co	Initial
Example Company	Final	8675.309

And I want to drag the client code up/down based on similar looking names. In some cases the names will be the same. If it's not 100% accurate and can only work on exact names that is ok too. The ideal scenario will be an outcome like this: 

Company Name	Type	Code
ABC Co	Initial	1234.100
ABC Company	Final	1234.100
ABC FS	Change	1234.100
XYZ Co	QC	5678.100
XYZ Co	Final	5678.100
Example Co	Initial	8675.309
Example Company	Final	8675.309

I've tried a few different options but I can't seem to get it to work, hoping for some new ideas. Some background - I've tried doing a lookup value from within powerbi (kept coming up blank), and also creating a separate query of just the "Finals" with the client codes and merging back to the original query - this broke the computer it took so long so I don't know if it worked or not. Any ideas would be much appreciated!

[ecarroll]

Thanks but I guess I should have said that the company names are much more complex than that so splitting with a delimiter is not an option, they need to stay as they are. 

I tried creating a new query of just finals from the original but now it will not allow me to merge them, the preview of the new query will not appear?

[Coriel-11]

I was worried that name-complexity might be a problem. How many names are you likely to be dealing with, where are they coming from and will new ones be being added continually?

[ecarroll · Accepted solution]

Around 6,000 but I'm really only concerned about 2,000 of them. They come from a Microsoft Form (people enter in names manually, and are not consistent when they do). Yes, they will continualy enter them in so I can't maintain any kind of master list. I do have a master list of the names but people don't always use the same name as the master list (in fact they rarely do). It's the different between saying Advertising Booking Company and ABC - sometimes they spell it out, sometimes they don't. I am going to add an optional client ID field to the form for people to fill in, but unfortunately I can't make it required, so I will keep running into this issue. 

[PC2790]

Try this code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVnDOV9JR8szLLMlMzAGyFJRidWASuQWJeZVAMbfMPLCcoZGxiZ6hgQFciVswUNQ5IzEvPRWuNSIyCmJmoDOmGMwkUzNzC7hJrhWJuQU5qdhdgpBEd42FmbmpnrGBpVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Company Name" = _t, Type = _t, Code = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Company Name", type text}, {"Type", type text}, {"Code", type number}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Company Name", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Company Name.1", "Company Name.2"}),
    #"Grouped Rows" = Table.Group(#"Split Column by Delimiter", {"Company Name.1"}, {{"All Rows", each Table.FillUp(Table.FillDown(_,{"Code"}),{"Code"}), type table [Company Name.1=nullable text, Company Name.2=nullable text, Type=nullable text, Code=nullable number]}}),
    #"Expanded All Rows" = Table.ExpandTableColumn(#"Grouped Rows", "All Rows", {"Company Name.2", "Type", "Code"}, {"Company Name.2", "Type", "Code"}),
    Custom1 = Table.AddColumn(#"Expanded All Rows", "Company Name", each [Company Name.1]&" " &[Company Name.2]),
    #"Removed Columns" = Table.RemoveColumns(Custom1,{"Company Name.1", "Company Name.2"})
in
    #"Removed Columns"

The outcome will be like this:

[Coriel-11]

I would does this in Power Query, by creating two tables and then merging them back togather as follows.
Table 1
Filter the original table down to just "type" = "Final" 

For the "Company Name" column, use split by delimiter on the first space, You'll get columns called "Company Name.1", "Company Name.2" etc.

Keep "Company Name.1" and Code and delete all the others 

Table 2

Create a new column that only contains the text up to that delimiter (using extract, but new columns by examples will probably do it  simply)

Then use that column to merge Table 1 into this column, joining the new column made above with "Company Name.1" column in Table 1.

Expand as necessary

Might give you problems if you have things like "The XYZ company". If so you might first need to do a series of Replace stages to filter these out.

Matt