How to use logical function (IF, AND) to check on 2 tables

D

dluhut

New member
Joined
Oct 11, 2011
Messages
6
Reaction score
0
Points
0
Hello,

I have 2 tables that I have 'imported' via Power Query as a 'Connection Only'.

The 1st table is multiple values of Account code, GL and Amount.

The 2nd table is where I'd like to bring in the 'Account Description' onto the 1st table.

The Logic is that, for each records in Table 1, if the Account Code is a match in Table 2 AND in Table 2[GL] is an asterisk (*) wild card, then return the "Account Code Description" of Table 2.

However, if the Table2[GL] is not an asterisk, then IT HAS TO BE AN EXACT MATCH and return the "Account Code Description" of Table 2.

For example, PL101 and PL201 which have multiple GL combinations in Table 1, will return as 'Revenue' and 'Other Revenue' from Table 2 "Account Code Description".

But for PL301, the PL and GL must match in order to return the correct "Account Code Description" from Table 2.

i.e. PL 301 AND GL 300200 returns 'Advertising'

Table 1(Below)
Account CodeGLAmount
PL10110010010
PL10110020020
PL10110030030
PL20120010040
PL20120020050
PL30130010060
PL30130020070
PL30130030080
PL30130040090


Table 2 (Below)
Account CodeGLAccount Code Description
PL101*(wildcard for ALL GL in Table1)Revenue
PL201*(wildcard for ALL GL in Table1)Other Revenue
PL301300100Advertising
PL301300200Advertising
PL301300300Marketing


Desired Output
Account CodeAccount DescriptionGLAmount
PL101Revenue10010010
PL101Revenue10020020
PL101Revenue10030030
PL201Other Revenue20010040
PL201Other Revenue20020050
PL301Advertising30010060
PL301Marketing30030070
PL301Advertising30020080
PL301Marketing30030090
 
You can merge the tables on Account Code and then select the Table2 record with matching GL, append this with the Table2 record with GL = "*" and take the description from the first record of the result.
This also allows for an Account Code with specific GL's plus a "*" for other GL's.

Code: 
let
    Source = Table1,
    Merged = Table.NestedJoin(Source,{"Account Code"},Table2,{"Account Code"},"Table2Records",JoinKind.LeftOuter),
    AddedDescription = Table.AddColumn(Merged, "Description", (This) => try Table.First(Table.SelectRows(This[Table2Records], each [GL] = This[GL])&Table.SelectRows(This[Table2Records], each [GL] = "*"))[Account Code Description] otherwise null),
    SelectAndReorderFields = Table.SelectColumns(AddedDescription,{"Account Code", "Description", "GL", "Amount"})
in
    SelectAndReorderFields
 
It works!

Thanks!

Is there a 'Reps Up' for you or anything? If yes, let me know so that I can increase your reputation.

Thread solved!
 
Last edited:
Question:
What is (This)? and where does it come from? How does this 'M' language knows what (This) refers to?

Also, if you can be kind enough to explain to me what does the line of code is doing.

(This) => try Table.First(Table.SelectRows(This[Table2Records], each [GL] = This[GL])&Table.SelectRows(This[Table2Records], each [GL] = "*"))[Account Code Description] otherwise null)

I only know that try ... otherwise null is just a check for error and if it's true, then return null value.
 
"(This)" is a parameter for a custom function that is used as "columnGenerator" for function Table.AddColumn.
Parameter columnGenerator determines the value for each row in the new Column.
Typically this looks like each [ColumnX] * 10 (i.e. the value in each row of the new column is 10 times the value in that row in column ColumnX).

The value that is passed into the columnGenerator is determined by the internal logic of function Table.AddColumn.
So the question is not how M knows what the parameter refers to, but how we know what the parameter refers to.
Well, I know that it refers to each record of the table, so that's why I gave it the name "This", but it could have been any valid identifier ("ThisRecord" or "Each" or "x" or ...).

The reason why I used (This) => instead of each, is because each is also used in the inner Table.SelectRows; with This it is possible to differentiate between each [GL] (from Table2) and This[GL] (from Table1).

The syntax of function Table.AddColumn is:
Code: 
Table.AddColumn(table as table, newColumnName as text, columnGenerator as function, optional columnType as nullable type) as table

The try ... otherwise does exactly what you described: in this case, it is possible that both Table.SelectRows return no rows, so then Table.First would return an error.
If you double check your input, then you'll see that there is no match for your last row with GL 300400 (which has suddenly become 300300 in your desired output).

On second thought, try ... otherwise wouldn't have been required, as function Table.First has a second parameter to provide a default value in case the table is empty, so that step can be rewritten as:

Code: 
    AddedDescription = Table.AddColumn(Merged, "Description", (This) => Table.First(Table.SelectRows(This[Table2Records], each [GL] = This[GL])&Table.SelectRows(This[Table2Records], each [GL] = "*"),[Account Code Description = null])[Account Code Description]),

There is no "rep's up" here.
 
Thanks MarcelBeug for your help!

I tried to use the same code to my actual data, and it seems that the 'Append' is very slow (taking at least 20 minutes). By any chance the Merge is making it slow?

What I have is 2013data, 2014data, 2015data, 2016data and 2017data (These are like Table1 as the above example). The number of rows/records per table is around 100k, so a total of just a little shy of 500k

And then I have tblCategory, which is Table2 as the above example.

So the code that I have is below

Code: 
let
    Source = Table.Combine({#"2013data", #"2014data", #"2015data", #"2016data", #"2017data"}),
    #"Renamed Columns" = Table.RenameColumns(Source,{{"Column2", "Company code desc"}, {"Column4", "Profit Center desc"}, {"Column6", "Account desc"}, {"G/L Account", "GL Account"}, {"Column8", "GL Account desc"}, {"Column10", "FA desc"}, {"Actual/Plan/Variance", "Actual/Budget"}, {"1", "Amount"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Company code", type text}, {"Company code desc", type text}, {"Profit Center", type text}, {"Profit Center desc", type text}, {"Account", type text}, {"Account desc", type text}, {"GL Account", type text}, {"GL Account desc", type text}, {"Functional area", type text}, {"FA desc", type text}, {"Actual/Budget", type text}, {"Amount", type number}}),
    [COLOR=#0000ff][B]#"Replaced Value"[/B][/COLOR] = Table.ReplaceValue(#"Changed Type","Plan #(lf)(Version 0)","Budget",Replacer.ReplaceText,{"Actual/Budget"}),
    
    Merged = Table.Buffer(Table.NestedJoin(#"Replaced Value",{"Account"},tblCategory,{"Account"},"Table2Records",JoinKind.LeftOuter)),
 
    AddedDescription = Table.AddColumn([U][B][COLOR=#ff0000]Merged[/COLOR][/B][/U], "Category", (This) => try Table.First(Table.SelectRows(This[Table2Records], each [GL Account] = This[GL Account])&Table.SelectRows(This[Table2Records], each [GL Account] = "*"))[PnL Category] otherwise null),
    #"Removed Columns" = Table.RemoveColumns(AddedDescription,{"Column1", "Table2Records"})
in
    #"Removed Columns"


2nd question, is it possible to 'replace' that red bold underline 'Merged' to be of the AppendTable? I tried to change 'Merged' to '#"Replaced Value"' which is above the Merged line, but it's giving null as the result for all rows/records.
 
You must log in or register to reply here.
Back
Top