Power Query Merge Problem

[Anonymous5]

Hello all,

I am using the Power Query function to collect data from our Procurement system database. To provide some background, our procurement system is set up as follows:

Create and Approve a Requisition --> Create and Evaluate RFQ --> Create, Issue and Deliver Purchase Order

These documents are all managed as rows of separate SQL tables. Each document has a unique 7-digit number. What I'm trying to do is merge the PO and Requisition tables together to establish a work flow to asses the cycle time through the whole process. Fortunately, the PO table has a column for the Requisition where it originated from so on the surface this merge seemed straightforward. The 7-digit Requisition number in the PO table should be an exact match back to its original entry in the Requisition table.

When I attempted this merge, however, I noticed that some of the POs are created using multiple Requisitions bundled together so the value in the PO table may appear as "1234567, 1234568" and so on. When this is the case, the Merge function is unable to recognize this and for a given Requisition, there is apparently no PO listed. Is there any way to allow the merge to "see" the correct Requisition among a comma-separated list in the PO table?

Let me know if any of this is unclear.

Thanks!

[#2]

@Anonymous5 

You need to have ID:s in separate rows to merge. To do that add new column splitting your texts with comma separated ID:s into the lists in that column, after that expand it and do merging on this new column. Generated script looks like

...
    //be sure the column has TEXT type
    #"Changed Type" = Table.TransformColumnTypes(
        Source,
        {{"Requisitions", type text}}
    ),

    // Add custom column splitting text to list
    // using comma as delimeter
    #"Added Custom" = Table.AddColumn(
        #"Changed Type",
        "Custom",
        each Text.Split([Requisitions],",")
    ),

    // expand new cusom column to rows
    #"Expanded Custom" = Table.ExpandListColumn(
        #"Added Custom",
        "Custom"
    ),

    // just in case trim it
    #"Trimmed Text" = Table.TransformColumns(
        #"Expanded Custom",
        {{"Custom", Text.Trim, type text}}
    )
...

Simple sample is attached.