Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
TestssonNow
New Member

Translate index match in Excel Power Query

Hi!

 

It is too heavy for Excel to execute the function =index(a:a, match(true, exact(b2, c:c), 0)) for +800 rows. I thus want to do this via Power Query in Excel instead (I loaded the data using get data from csv); after trying for hours and even asking ChatGPT for help, I am unable to solve this.

 

Per row value in column B, I want to search for that row value in column C. If found, I want a new custom column to get the corresponding value in Column A.

 

Example:

Column A         Column B       Column C

Text1                 1234              5678

Text 2.                2346            6789

Text 3.                5678              73785

Text 4

 

So when iterating the first time, the Custom column would have value Text3. 

7 REPLIES 7
ronrsnfld
Super User
Super User

Using a similar algorithm as @m_dekorte , and generating a CSV file with 800,000 rows, and only single matches between Columns B & C, the PQ took about 15 seconds to run. (It took longer to write the results back to the Excel worksheet). I did not get any out of memory errors.

 

M-Code: Paste into Advanced Editor

let

//Change next line to reflect actual file path
    Source = Csv.Document(File.Contents("C:\Users\ron\Documents\test.csv"),[Delimiter=",", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column A", type text}, {"Column B", Int64.Type}, {"Column C", Int64.Type}}),

//Add index column to be able to re-sort into original order
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),

//Join the table to itself, using different columns for the "key"
    #"Self-Join" = Table.NestedJoin(#"Added Index","Column B",#"Added Index","Column C","Index Match",JoinKind.LeftOuter),

//Expand the joined table Column A
    #"Expanded Index Match" = Table.ExpandTableColumn(#"Self-Join", "Index Match", {"Column A"}, {"Index Match.Column A"}),

//Sort back to original order
    #"Sorted Rows" = Table.Sort(#"Expanded Index Match",{{"Index", Order.Ascending}}),

//Remopve Index column
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
    #"Removed Columns"

Partial Results

 

ronrsnfld_0-1718974362933.png

 

How much memory do you have?

Are you running Excel 32 bit or 64 bit?

ronrsnfld
Super User
Super User

Will there only be a single match? Or might there be multiple matches?

 

Also, why would you expect Text 3 as a result? Your formula in Excel would return Text 1.

foodd
Super User
Super User

@TestssonNow thank you for your post, and leveraging Power Query to handle this for which it is aptly able.   @m_dekorte is a rockstar, and her crazy wonderful work and  warm gentle touch with folks on solutions is quite a gift.  You are in good hands.  Thank you!

TestssonNow
New Member

Hi!

 

Thanks for the reply. What do you mean by extracting column 1? Also, in your image your extra column shows text1 instead of text3 which is the desired result. 

Hi @TestssonNow 

 

Select Merge on the ribbon, merge with the itself

m_dekorte_1-1718913449887.png

 

That yields a nested table as result, which can be expanded, with the sideward arrows

m_dekorte_0-1718913389966.png

 

Select Column1, from the available fields

m_dekorte_2-1718913577502.png

 

Thank you for the explanation, much appreciated! I have a dataset with +800k rows and that seemed to be too much with the expansion solution (i.e. Power Equery says it ran out of memory and doesnt complete the process). Is there an m code / Power Query Custom column code I could use to solve this?

m_dekorte
Super User
Super User

Hi @TestssonNow,

 

You can achieve this through a self-merge

m_dekorte_0-1718906881370.png

 

And subsequently expanding/Extracting "Column1" from the result table.

m_dekorte_1-1718906975844.png

 

I hope this is helpful

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors