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

[dluhut]

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 Code	GL	Amount
PL101	100100	10
PL101	100200	20
PL101	100300	30
PL201	200100	40
PL201	200200	50
PL301	300100	60
PL301	300200	70
PL301	300300	80
PL301	300400	90

Table 2 (Below)

Account Code	GL	Account Code Description
PL101	*(wildcard for ALL GL in Table1)	Revenue
PL201	*(wildcard for ALL GL in Table1)	Other Revenue
PL301	300100	Advertising
PL301	300200	Advertising
PL301	300300	Marketing

Desired Output

Account Code	Account Description	GL	Amount
PL101	Revenue	100100	10
PL101	Revenue	100200	20
PL101	Revenue	100300	30
PL201	Other Revenue	200100	40
PL201	Other Revenue	200200	50
PL301	Advertising	300100	60
PL301	Marketing	300300	70
PL301	Advertising	300200	80
PL301	Marketing	300300	90

 

[MarcelBeug]

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.

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

 

[dluhut]

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!

 

[dluhut]

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.

 

[MarcelBeug]

"(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:

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:

    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.

 

[dluhut]

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

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.