match and index in power query M language

[gavin007]

I have an interesting data transformation task using match and index formula. I am wandering if it can be done in pwoer query.

• I need to convert some internal sales customer with duplicate items into external customer . (row 6 and 😎
• I use Match to find out which row that duplicate item exist ( in this case, row 2, 5, 6 and 😎
• Use Index and IF formula to find and replace the name of customer only if it is internal customer with duplicate.

Pain point: I can't replicate the match and index formula in column F and G in power query. (as below)

Can anyone help? I can upload the excel sample file if needs. but i don't know how to upload it.

[Anonymous · Accepted solution]

the result is more naturally achieved using the add.column function and some ifs, but I wanted to find an excuse to use Table.ReplaceMatchingRows

let
    Source = ...

 Table.AddColumn(Source, "Custom", each if [ExtInt]="Int" then try Source{[ExtInt="Ext",Item=[Item]]}[Customer] otherwise [Customer] else [Customer])


in
#"Added Custom"

[gavin007]

First of all, thanks for all the reply I got from everyone here. I am a bit overwhelmed. I have to admit though, my plan of just amending the code with a bit to fit into my existing one fail miserably. I realised I want to simplify the example gives me error here and there when I put it back to my own code.

Simple put, I now create a test pbix file and two excels files. If you can add the code at the end of mine that will be really appreciated. sorry for the pain.

Here is the link of the folder.

https://1drv.ms/u/s!Aig3EWdV94jKgexzGVvzpRXzm-3hcQ?e=eaP7Ta

If you are wandering what is my code trying to do, I combined project sales and normal sales together and create a column to count duplicate item. Now in the combined daily sales query, I need to

If extint column is "I" and duplicate column is <>0, then replace the name in customer name column to the one with "C" in Extint column and same item in column. Same Rule as my previous sample file. 

[ziying35]

@gavin007 

let
    Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("i65Wcq0o8cwrUbICMZR0lJxLi0vyc1OLgAJe+Rl5QBHPktRcIM9RqVYHn2qn/CSEYmcCioNLUstSEcpdCSj3SKzMSa1EqHdCVQ8iUdR7J5akEq86PLEyD0m5GwHlTqmooRILAA==",BinaryEncoding.Base64),Compression.Deflate))),
    dic = let ext_tb= Table.SelectRows(Source, each ([ExtInt] = "Ext")) in Record.FromList(ext_tb[Customer], ext_tb[Item]),
    result = Table.ReplaceValue(Source, dic, each [Item], (x,y,z)=>Record.FieldOrDefault (y, z, x), {"Customer"})
in
    result

[v-alq-msft]

Hi, @gavin007 

Based on your descripton, I created data to reproduce your scenario. The pbix file is attached in the end.

You may create an index column in 'Query Editor' as below.

Here is the codes in 'Advanced Editor'.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXKtKAGSXvkZeUDKEYgNlWJ1opVM4FJO+UlA0hmIDcAyZnCZ4JLUslQQDy5nDpfzSKzMSa0EaYcbaQRkeeaBJL0TS1JRpIzhUuGJlXkgOTe4nClczikVyY2xAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, #"Ext Int" = _t, Customer = _t, Item = _t, Duplicate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Ext Int", type text}, {"Customer", type text}, {"Item", type text}, {"Duplicate", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index.1", 1, 1, Int64.Type),
    Custom1 = Table.AddColumn(#"Added Index","Which row duplicate",
each let 
item= [Item], index=[Index.1], ExtInt=[Ext Int],
tab = Table.SelectRows(#"Added Index",each [Item]=item and [Index.1]<>index and [Ext Int]<>ExtInt)
in
if 
Table.RowCount(tab)>0
then
Table.Column(
    tab,
    "Index.1"
){0}
else
[Index.1]
),
Custom2 = Table.AddColumn(Custom1,"Result Customer",
each let 
item= [Item], index=[Index.1], ExtInt=[Ext Int],
tab = Table.SelectRows(#"Added Index",each [Item]=item and [Index.1]<>index and [Ext Int]<>ExtInt)
in
if [Ext Int]="Int"
then
    if 
    Table.RowCount(tab)>0
    then
    Table.Column(
        tab,
        "Customer"
    ){0}
    else
    [Customer]
else
[Customer]
)
in
    Custom2

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jimmy801]

Hello @gavin007 

power query hasn't the same logic as Excel. So you have to use power-query function to get to the result. List.PositionsOf is Match-Function in Excel. Combining this with List.Select and List.Count to identifiy duplicates and the List-Row-identifier {} will bring you the result.

Here an example I prepared for you

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcq0oUdJR8srPyANSjkqxOjAhp/wkIOmMJBJcklqWCqRdkcQ8EitzUitBysGCnnkgQe/EklQ0ofDEyjyQmBuSmFMq1M5YAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ExtInt = _t, Customer = _t, Item = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ExtInt", type text}, {"Customer", type text}, {"Item", type text}}),
    AddColumn = Table.AddColumn(#"Changed Type","Check", (add)=> if List.Count(List.Select(#"Changed Type"[Item], each _ = add[Item] ))>1 and add[ExtInt]="Int" then #"Changed Type"[Customer]{List.PositionOf(#"Changed Type"[Item],add[Item])} else add[Customer])
in
    AddColumn

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[gavin007]

Hi Jimmy,

Ignore my previous reply. I tried your script, it add a function column for some reason. 

Here is the pbix file.

https://1drv.ms/u/s!Aig3EWdV94jKgexzGVvzpRXzm-3hcQ?e=7nIhMn

Can you have a look? Thanks.

[Anonymous · Accepted solution]

Hi @gavin007 

I'm not able to open your pbix file (peraphs because I have old version of PBI: I can't update because I'm behind proxy corporate)then I can only see your screen shot.

From this I'm not sure that what are you using is "my" approach.

I suggest to start from scratch table and via GUI use tool to add new column give a name you like and in the field code put this

if [ExtInt]="Int" then try Source{[ExtInt="Ext",Item=[Item]]}[Customer] otherwise [Customer] else [Customer])

eventually changin the name of columns: ExtInt, Item and Customer

pay attention to the name ot table. here is referenced as "Source"  change it if needs.

PS

In can see only part of your code from screen shot.

from what I can see, I suppose the reason why you get function as values of your added column is due to the function (add)=> which follows the function each.

[gavin007]

HI Rocco, it works after i take out the each before the if. It is just try and error. This is what i don't understand M language, it have the beauty of use mouse to create some script, but not that sleak when I want to read it or amend it. Maybe it is me still learning this language.

Thanks anyway.

[gavin007]

HI Jimmy, I try it it works but when I put it in my existing query, it comes back with the error message. Expression.Error: The name 'AddColumn' wasn't recognized. Make sure it's spell correctly. I just copy and paste and change the refernce to previous line. Is this AddColumn like a variable?

[Anonymous]

just to use List.Accumulate in case where doesn't flow over

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcq0oUdJR8srPyANSjkqxOjAhp/wkIOmMJBJcklqWCqRdkcQ8EitzUitBysGCnnkgQe/EklQ0ofDEyjyQmBuSmFMq1M5YAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ExtInt = _t, Customer = _t, Item = _t]),


  extItems = List.Accumulate(
      itemsIntExt, 
      Source, 
      (s, c) => Table.ReplaceMatchingRows(
          s, 
          {
            Source{[ExtInt = "Int", Item = c]}, 
            Source{[ExtInt = "Ext", Item = c]} & [ExtInt = "Int"]
          }
        )
    ),
  itemsIntExt = List.Intersect(
      {
        Table.SelectRows(Source, each [ExtInt] = "Ext")[Item], 
        Table.SelectRows(Source, each [ExtInt] = "Int")[Item]
      }
    )
in
  extItems

[Anonymous · Accepted solution]

the result is more naturally achieved using the add.column function and some ifs, but I wanted to find an excuse to use Table.ReplaceMatchingRows

let
    Source = ...

 Table.AddColumn(Source, "Custom", each if [ExtInt]="Int" then try Source{[ExtInt="Ext",Item=[Item]]}[Customer] otherwise [Customer] else [Customer])


in
#"Added Custom"

[gavin007]

HI Rocco, Your approach come very close to success, but it return as a expandable table in the custom column. I saved the your approach in the the pbix file in the new link. 

https://1drv.ms/u/s!Aig3EWdV94jKgexzGVvzpRXzm-3hcQ?e=7nIhMn

Can you shed some light on this?

[Fowmy]

@gavin007 

Can you share some sample data and the expected result to have a clear understanding of your question?
You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.

____________________________________
How to share sample data?
How to get your questions answered quickly?

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[gavin007]

Hi Fowmy, https://1drv.ms/x/s!Aig3EWdV94jKgexgT5LJ180w-pfQcw?e=CXoCmK  Please find the link. Thank you.