"Vlookup Like"

[CPIBecklon]

Hello! Please note the two tables below. Actual refers to a query that I have.

In the query, there is an activity column. Activities are A,B, and C.

There is also a Unit # column. Units are simply machines that are being assigned a value.

Machine # column, however, refers to the type of machine. For example, a 1 may mean that it is an Acme machine, while 2  may mean that it is a Bestbrand machine.

As you can see, the Machine # will only appear with Activity C. Unit #, however, appears with Activity B and C. Is there a simple way within the query that I can make the corresponding Machine numbers appear with Activity B? The "Ideal" table shows what I am trying to achieve. I realize that I could duplicate the query, filter out Activity C, group data, and then pull the data back into the original query...but this makes the Query run slower. I am hoping for a simple fix within the original query, but at this point, I am thinking it is not possible.

I tried the idea of, more or less, duplicating the unit column and the machine column. In the duplicate columns, I filled up. Then I wrote a condition that said "If Unit Column 1=Unit Column 2 and Machine Column 1 = Machine Column2, then return Machine Column 2." Unfortunatley, this will not yield the results I want. I cannot sort the data because this too will impact the results. I would greatly appreciate any input on how to handle this.

[jbwtp]

Hi @CPIBecklon,

can this be done using the join (merge) operation from the main menu?

Cheres,

John

[CPIBecklon]

@jbwtp I should have mentioned above that I did try the suggestion you made. My hope is to streamline the process. Merging seems to make the process slower. At this point, however, I don't think there is a better way. Thank you for the suggestion.

[jbwtp]

Hi @CPIBecklon,

generally speaking Merge is the best as it is clearer what you do. You can try this idea to see if it runs any faster:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQKhWJ1oJScgwwiZYwzjOEM5hnCeERjHxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Activity = _t, Unit = _t, Machine = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Activity", type text}, {"Unit", Int64.Type}, {"Machine", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Unit"}, {{"Count", each Table.ReplaceValue(_,"","",(x, y, z)=> List.Max(_[Machine]),{"Machine"})}}),
    Custom1 = Table.Combine(#"Grouped Rows"[Count])
in
    Custom1

Kind regards,

John

[CPIBecklon]

@jbwtp 

John, First let me thank you! Your suggested process started to me to thinking of how to handle this. Maybe your suggestion was the same of what I did. I Grouped my data by Unit # and Machine #. I did not include the activity because Activity A does not capture either unit or machine. By Grouping Unit and Machine, I would assuredly capture  Activities B and C. Please also note that when I built the grouping, I had a final Table Column to reintroduce all the other fields that existed before this step. After I grouped, I sorted the units in ascending order and then the machines in descending order. I then filled the column downward. This then pushed the machine data from the Activity C Rows into the unit data from the Activity B rows. I then expanded the table to include other field that were previously included. Did it work? I am not finding that all Activity B's had a machine listing after doing this. I need to investigate why. While this process seemed like a good idea, it still seems to have taken a while to process the data. Thus, I am thinking you are correct to suggest the merge over the grouping. Thank you for your help with this!