The mock-up below should hopefully give you a strong idea of the result that I am seeking. To achieve that result, the solution will have to do something like the following. For every PO ID in table 1 – based on the Bid No.(s) extracted from the free text in the second column – match to the BID ID (and its attributes) found in Table 2. Then calculate counts and sum $ noted in the mock-up. Note that the bottom 3 rows in Table 1 contain more than one Bid number, with special character separations (like “, &, and” …) – that is the key part of the problem that I am experiencing.
First off, great job laying out the problem clearly and providing all the relevant information, files and mockup of desired result. Makes providing good support MUCH easier.
Second, fun problem. I believe this is the result you’re looking for:
I addressed the problem with the multiple delimiters in your PO file by creating a short custom function based on Splitter.SpitTextByAnyDelimiter, which allowed me to split the column properly despite the inconsistency in the delimiter used
I then unpivoted the original Bid table, and did some cleaning to get the table into the proper “long and thin” shape prior to the merge on Bid ID.
Once PQ did all the heavy lifting, DAX was just left to handle some simple calculations.
Count of BID Ids (Matched) = COALESCE( DISTINCTCOUNTNOBLANK( Merged[Bid No. Last 5 Digits] ), 0 )
Thanks, works beautifully. As shown below, I added modified Count DAX formulas for Vendor, in order to achieve the ‘correct’ vendor count totals: see example of PO ID 13013. I did it via two different DAX formula approaches, hopefully neither is problematic.
Thanks again. Just one more thing, which just occurred to me and I should have made more clear from the start.
The ‘PO ID’ table is from a system totally separate from that of the ‘Bids database’. So one other objective of the reporting is to validate the ‘Bid No.’ manually entered into the PO ID table against the ‘Bids database’ BID ID field. As you can imagine, bad values can get entered.
So I expanded the mock-up .PBIX to include a 'Bid No." that does not match to any BID ID field in the ‘Bids database’. Based on how the data model stands now, it falsely counts the PO ID table as containing a valid Bid No:
On a related note, when you unpivot the ‘2 Bids Solution’ table it drops BID ID rows that contain no Vendor name or Vendor values. I think we’d want to retain those rows, in order to perform a complete Bid No. validation per above. I realize this would change the subsequent steps required for aligning Vendor Name and Value on the same row! … I would need your assistance with that as well.
Thanks so much. If I can somehow get your email, I’ll make sure you’re stocked with 3 months of Moutain Dew (or equivalent)!
Please review this power query solution, it get’s you the expected result.
I created a nested Record which pre-calculates the required values
Table.AddColumn( MergeBidsTable, "Custom", each
let
myPo = [#"PO ID (unique)"],
myVal = [Extract Bid ID chars]
in
[
Count matched Bids = if not List.Contains( lBids, myVal) or myVal = null then 0 else List.Count(List.Select( lPOs, each _ = myPo)),
Count of Vendors= Table.RowCount([BidsTable]),
Total Bids = List.Sum([BidsTable][Bid])
]
)
Hi @mdalton2100, good to see that you are having progress with your inquiry.
Did the response provided by @Melissa and @@BrianJ help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.
I hope that you are having a great experience using the Support Forum so far. Kindly take time to answer the Enterprise DNA Forum User Experience Survey, we hope you’ll give your insights on how we can further improve the Support forum. Thanks!
Hi @mdalton2100, we’ve noticed that no response has been received from you since July 29. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.
