Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Good morning, afternoon, night and a big thank you for the strong community which already helped me a LOT with multiple topics.
I am relatively new to Power Query and Power BI desktop so please bear with me.
Basically i am trying to reconcile three different IT systems and their output for my finance team.
In an ideal world all three systems are communicating in a proper manner with linear attributes with each other.
In my case they don’t.
So what i get and import into Power Query are two files, Import 1 and Import 2 (attached excel)
Import 1 generates invoices with a 8 digit invoice number. Not only invoice numbers but sometimes including additional manual comments.
Import 2 same.
Though both tables always contain at some point an invoice number.
What I would usually do in Excel is go =SUMIFS(sum_range,criteria_range1,criteria1) with criteria1 being the value from cell A1.
Yet this does not work in power query since CONTAINS only works (to my knowledge) for specific values in “”. Not for column to column search.
So what I am aiming for is something that does IF Import 1 Contains value A2 from Import 2 then sumif of Import 1 column 2.´
I already tried separators (which does not work since some manuals don’t have enough separators), text to column then merging it.
Everything ends on the manuals.
Thanks and happy to get feedback or questions
Hi @EmBee ,
You can create a custom function that gets the text from a row of column B (e.g. B2) and the whole column of invoice numbers (column D) as input, and then for each row of the invoice number column compares the text using Text.Contains, and if there is a match, the function returns the matching invoice number (or list of invoice numbers if a cell in column B could potentially contain more than one invoice number).
Alternatively, you can use the List.Contains function and leverage the custom comparer as described here to compares each invoice number with the füll text from column B. But this function will only return true or false, meaning the text in column B contains a valid invoice number or not.
BR
Martin
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
21 | |
16 | |
15 | |
11 | |
7 |
User | Count |
---|---|
25 | |
24 | |
12 | |
12 | |
12 |