Merge/Lookup Using Conditions

Show me the full query M code, and use the code box if possible - the </> icon.

But I think the issue is the last two lines in your full query are:

in
   Source

Should be:

in
    #"Application_Versions"

but I'd need to see the full M query to be sure. I'm only seeing snippits.

EDIT: Actually, nevermind. Your issue is this:

Table.NestedJoin(
#"Application_Versions",

but I cannot tell you how to fix it without seeing the full M code.

Sorry, I really appreciate your help but I am just not following what you are asking for, I have sent all code that I have used, not just snippets

After doing the initial merge, without conditions, the following is pasted into the formula bar to add the condition

= Table.NestedJoin(
            #"Application_Versions", 
            {"state"}, 
            Table.SelectRows(#"LookupTable", each [CodeSet] = "TrueFalse"), 
            {"Value"}, 
            "LookupTable", 
            JoinKind.LeftOuter
        )

At this point the advanced editor has the following

let
    Source     = 
        Table.NestedJoin(
            #"Application_Versions", 
            {"state"}, 
            Table.SelectRows(#"LookupTable", each [CodeSet] = "TrueFalse"), 
            {"Value"}, 
            "LookupTable", 
            JoinKind.LeftOuter
        )
in
    Source

I then expand the columns of the merged table to pick DisplayValue

I then go ahead and do another merge without conditions and once complete, paste in the adjusted code for apptype codeset

= Table.NestedJoin(
            #"Application_Versions", 
            {"type"}, 
            Table.SelectRows(#"LookupTable", each [CodeSet] = "AppType"), 
            {"Value"}, 
            "LookupTable", 
            JoinKind.LeftOuter
        )

I am presuming you are suggesting that the first merge should be adjusted to include the 2nd condition, rather than doing a 2nd merge on the new condition.

I dont follow the code you sent or where you are suggesting this should go, or in fact what point in the process is it used (if at all). ie do I do a normal merge and then paste over with that code (like I have been doing, but instead with 2 conditions), or is it something else I should be doing?

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", Int64.Type}, {"State", Int64.Type}}),
    #"Merged Queries" = 
        Table.NestedJoin(
            #"Changed Type", 
            {"Type"}, 
            Table.SelectRows(#"Lookup Table", each [Codeset] = "AppType"), 
            {"Value"}, 
            "Lookup Table", 
            JoinKind.LeftOuter
        ),
    #"Expanded Lookup Table" = Table.ExpandTableColumn(#"Merged Queries", "Lookup Table", {"DisplayValue"}, {"DisplayValue"}),
    #"Merged Queries1" = 
        Table.NestedJoin(
            #"Expanded Lookup Table", 
            {"State"}, 
            Table.SelectRows(#"Lookup Table", each [Codeset] = "TrueFalse"), 
            {"Value"}, 
            "Lookup Table", 
            JoinKind.LeftOuter
        ),
    #"Expanded Lookup Table1" = Table.ExpandTableColumn(#"Merged Queries1", "Lookup Table", {"DisplayValue"}, {"DisplayValue.1"})
in
    #"Expanded Lookup Table1"

Your code above

Let's back up a second sprotson 😁

Let me post my full M code here:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwoMFTSUQJhA6VYHbCAEbqAMZADEYQKmKALmAI5xhCBWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Type = _t, State = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", Int64.Type}, {"State", Int64.Type}}),
    #"Merged Queries" = 
        Table.NestedJoin(
            #"Changed Type", 
            {"Type"}, 
            Table.SelectRows(#"Lookup Table", each [Codeset] = "AppType"), 
            {"Value"}, 
            "Lookup Table", 
            JoinKind.LeftOuter
        ),
    #"Expanded Lookup Table" = Table.ExpandTableColumn(#"Merged Queries", "Lookup Table", {"DisplayValue"}, {"DisplayValue"}),
    #"Merged Queries1" = 
        Table.NestedJoin(
            #"Expanded Lookup Table", 
            {"State"}, 
            Table.SelectRows(#"Lookup Table", each [Codeset] = "TrueFalse"), 
            {"Value"}, 
            "Lookup Table", 
            JoinKind.LeftOuter
        ),
    #"Expanded Lookup Table1" = Table.ExpandTableColumn(#"Merged Queries1", "Lookup Table", {"DisplayValue"}, {"DisplayValue.1"})
in
    #"Expanded Lookup Table1"

ANd the code for the Lookup Table (the above returns errors until both tables are in Power Query)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJxAhKOBQUhlQWpSrE60UpGQH5walFZahGahDFIdWpxdkl+AZqMAZAfEhTqCqKKSlPdEnOKIRIgC9wcfYLRZGIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Value = _t, DisplayValue = _t, Codeset = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Value", Int64.Type}})
in
    #"Changed Type"

So above, the steps are as follows:

1. Source and Changed Type are just me pasting data into the model. You would have a real data source there and may or may not have any Changed Type. SQL Server doesn't require this for example.
2. Merged Queries is the first merge. I did this through the UI with no conditions. THen, I edited the code to remove the reference to just the #"Lookup Table" and replaced it with a filtered table using Table.SelectRows(#"Lookup Table", each [Codeset] = "AppType")
3. Then I expanded the column I wanted, DisplayValue.
4. Then I created another merge through the UI and it built the formula for me. Again, I got rid of the #"lookup Table" refrence and replaced that with another filtered table - Table.SelectRows(#"Lookup Table", each [Codeset] = "TrueFalse")
5. Then I expanded the DisplayColumns column again.

Nothing gets lost. it keeps adding the columns. 

If you are seeing things go missing, it is likely because you are editing the code manually and not taking the first parameter of all Table.XXXX() functions into account. If you replaced my 2nd Merged Queries above with this:

    #"Merged Queries1" = 
        Table.NestedJoin(
            #"Changed Type", 
            {"State"}, 
            Table.SelectRows(#"Lookup Table", each [Codeset] = "TrueFalse"), 
            {"Value"}, 
            "Lookup Table", 
            JoinKind.LeftOuter
        )

it would be going back to the #Changed Type step skipping the first Merged Queries step, so in that case, the first merge will disappear from your results, becuse you jumped back to an earlier table. #Changed Steps is not the name of a step, it is the name of the table that you changed the data types of. Merged Queries is not the name of a step, it is the name of a table that has a merge operation in it. 

Note that "step names" can also be lists, records, and scalar values depending on what you are doing, but they are always an object of some sort.

Does that help?

I really appreciate all your help and especially the time you have spent on this, but I simply dont follow the steps you are taking and more specifically the exact code that you are typing in on each step.

Is it possible for you to do a short screen record to show me the process as I am not getting this, and details exactly the code you are etering in each step.

I think seeing it visually would help at lot

If you are not able to do this, and I certainy understand it is a big ask, then I think I will probably go with the option of duplicating the lookup table, filtering and merging multiple times

Thanks

I think this might be my M code after doing the second merge and then adjusteing the formula

// Application_Versions
let
    Source = Table.NestedJoin(ApplicationCME, {"Key"}, ConfigurationItemVersion, {"Key"}, "ConfigurationItemVersion", JoinKind.Inner),
    #"Expanded ConfigurationItemVersion" = Table.ExpandTableColumn(Source, "ConfigurationItemVersion", {"AddedDate", "UpdatedBy", "IsDeleted"}, {"ConfigurationItemVersion.AddedDate", "ConfigurationItemVersion.UpdatedBy", "ConfigurationItemVersion.IsDeleted"})
in
    #"Expanded ConfigurationItemVersion"

// LookupTable
let
    Source = Sql.Database("77.74.194.165,49000", "GK_Test_649_Spotlight_Demo1", [CreateNavigationProperties=false]),
    dbo_LookupTable = Source{[Schema="dbo",Item="LookupTable"]}[Data],
    #"Filtered Rows" = Table.SelectRows(dbo_LookupTable, each true)
in
    #"Filtered Rows"

// Merge1
let
    Source = Table.NestedJoin(
            #"Application_Versions", 
            {"type"}, 
            Table.SelectRows(#"LookupTable", each [CodeSet] = "AppType"), 
            {"Value"}, 
            "LookupTable", 
            JoinKind.LeftOuter
        ),
    #"Expanded LookupTable" = Table.ExpandTableColumn(Source, "LookupTable", {"DisplayValue"}, {"LookupTable.DisplayValue"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded LookupTable",{{"LookupTable.DisplayValue", "AppType"}}),
    #"Merged Queries" = Table.NestedJoin(
            #"Application_Versions", 
            {"state"}, 
            Table.SelectRows(#"LookupTable", each [CodeSet] = "TrueFalse"), 
            {"Value"}, 
            "LookupTable", 
            JoinKind.LeftOuter
        ),
    #"Expanded LookupTable1" = Table.ExpandTableColumn(#"Merged Queries", "LookupTable", {"DisplayValue"}, {"LookupTable.DisplayValue"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Expanded LookupTable1",{{"LookupTable.DisplayValue", "StateEnabled"}})
in
    #"Renamed Columns1"

Use this M code for Merge 1.

// Merge1
let
    Source = Table.NestedJoin(
            #"Application_Versions", 
            {"type"}, 
            Table.SelectRows(#"LookupTable", each [CodeSet] = "AppType"), 
            {"Value"}, 
            "LookupTable", 
            JoinKind.LeftOuter
        ),
    #"Expanded LookupTable" = Table.ExpandTableColumn(Source, "LookupTable", {"DisplayValue"}, {"LookupTable.DisplayValue"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded LookupTable",{{"LookupTable.DisplayValue", "AppType"}}),
    #"Merged Queries" = Table.NestedJoin(
            #"Renamed Columns", 
            {"state"}, 
            Table.SelectRows(#"LookupTable", each [CodeSet] = "TrueFalse"), 
            {"Value"}, 
            "LookupTable", 
            JoinKind.LeftOuter
        ),
    #"Expanded LookupTable1" = Table.ExpandTableColumn(#"Merged Queries", "LookupTable", {"DisplayValue"}, {"LookupTable.DisplayValue"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Expanded LookupTable1",{{"LookupTable.DisplayValue", "StateEnabled"}})
in
    #"Renamed Columns1"

Both of your Table.NestedJoin() functions were using #"Application Versions" as the first table. So the 2nd join was ignoring the first. Your 2nd join needs to be based on the "Renamed Columns" table - the step right above it.

Thanks - that makes sense now

Each time I do a new merge (to add 1 more column), I need to Merge as New with the formula reference being to the previous merged table. That way the previously added column (from the previosu merge) remains

Its a solution using formulas rather than filtering, but it still means having multiple Merge tables, as opposed to multiple LookUp tables (filtered). Lets say I have 10 distinct lookup codes, so I would have to do 10 merges - which gives the same number of queries as when I have 10 seperate filtered look up tables.

I think I may go for the original solution, as I think it is easier to manage, and actually the filtered lookup tables can then be merged with other tables in my model if required - as some of the codes are used elsewhere

Ideally, it would be good to have a formula that allows me to do 1 merge and add multiple columns all from the same table - ie AppType, State etc from a single formula/merge

I dont think that is a merge, more like a conditional lookup of some sort

Is there any sort of formula that can be used in a "Custom Column" that would allow me to grab a column from the lookup table based on a condition, and then add another custom column based on another condition?

Any thoughts on if I could use a cusotm column to retrieve the data rather than a merge?

I really dont know anything about M Code, so am pretty clueless where to start

A few things:

"Each time I do a new merge (to add 1 more column), I need to Merge as New with the formula reference being to the previous merged table. That way the previously added column (from the previosu merge) remains

Its a solution using formulas rather than filtering, but it still means having multiple Merge tables, as opposed to multiple LookUp tables (filtered). Lets say I have 10 distinct lookup codes, so I would have to do 10 merges - which gives the same number of queries as when I have 10 seperate filtered look up tables."

You just merge as normal. Then you edit the code. You are trying to do everything in the advanced editor and that is how your 2nd Merge referenced the original table. Everything in M is formulas. Everything. A filter is the formula using the Table.SelectRows() function. A merge is a formula using the Table.NestedJoin() function.

You can do all 10 "lookup codes" in one merge. Your Table.SelectRows would look like this:

Table.SelectRows(SourceTable, each ([Field1] = 1) and ([Field2] = 2) and ([Field3] = 3))

And so on.

But I agree, if pre-filtering the table for other uses makes more sense to you, do it that way. There is right or wrong, there is better or worse from the standpoint of which is easier to maintain over time, and which one mitigates the possiblity of error better.

I don't know why you think what I posted isn't a merge. It is, with a condition. I have a blog post coming out on this on Sept 8 on my site, but as a spoiler, what I posted folds, and folds as a merge. This is the SQL statement Power Query folds to the SQL Server. It pre-filters the table, then does the merge, in one step. Server does all of the work. It still works for flat files and non-foldable sources as well, it will just be slower because 100% of the work has to be done by Power Query - that is the case no matter which method you use.

You do not, in my opinion, want to go down the path of "lookup columns" in Power Query. It is not efficient and lookups in other tables, 10,000 rows? No problem. 1,000,000 rows? It will never finish. Power Query isn't Excel and VLOOKUP and their equivalents, while possible in Power Query, are generally very inefficient. I avoid them on all but the smallest of models.

''Ideally, it would be good to have a formula that allows me to do 1 merge and add multiple columns all from the same table - ie AppType, State etc from a single formula/merge"

I don't understand this comment at all. You can always expand any/all columns from the merged (2nd) table as long as your merge condtion is the same. You cannot expand column 1 if field 1 is "A" and column 2 if field 2 is "B" - that is two seperate merges as you cannot merged based ([Field 1] = "A") and ([Field 2] = "B") as that logic is not what you want. You are thinking in terms of Excel having a compound formula with a bunch of nested IF() statements, or an IFS() statement. Again, Power Query isn't Excel. You could do that in DAX using a bunch of IF() with LOOKUPVALUE() functions, but that would get slow as well as difficult to maintain. I doubt I'd go down that path. Do data modeling in Power Query or your source system, not in DAX.

Thanks - I think I will be doing this with multiple lookup tables, each filtered

The reason I need to do multipel merges, is that all the data I want to expose is in the same column of the lookup table. I merge based on 1 condition, expose the column, merge on another condition, then expose the same column again.

Below is a sample of what would be in the lookup table, as you see, all the values I want to retrieve and diaply in different columns are all within the "DisplayValue" column

Here is the ultimate results of doing 3 merges

1 is state=value and codeset - TrueFalse

2 is Type=values and codeset = AppType

3 is Server=value and codeset - FalseTrue

Columns 2,3 & 4 are from the original query and columns 5,6 & 7 are the exposed DisplayValue columns from merges 1, 2 & 3

I hope that makes sense why I think I need to do multiple merges, as the data is always in the same column each time

As I said previously, having multiple lookup tables (each filtered) is similar to what I would need to do in sql - so I will be going with that methodologu

Thanks for your help