Home

Power Query Merge Problem

%3CLINGO-SUB%20id%3D%22lingo-sub-973686%22%20slang%3D%22en-US%22%3EPower%20Query%20Merge%20Problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-973686%22%20slang%3D%22en-US%22%3E%3CDIV%3E%3CDIV%20class%3D%22%22%3E%3CDIV%20class%3D%22public-DraftStyleDefault-block%20public-DraftStyleDefault-ltr%22%3E%3CSPAN%3EHello%20all%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CDIV%20class%3D%22%22%3E%3CDIV%20class%3D%22public-DraftStyleDefault-block%20public-DraftStyleDefault-ltr%22%3E%3CSPAN%3EI%20am%20using%20the%20Power%20Query%20function%20to%20collect%20data%20from%20our%20Procurement%20system%20database.%20To%20provide%20some%20background%2C%20our%20procurement%20system%20is%20set%20up%20as%20follows%3A%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%20class%3D%22public-DraftStyleDefault-block%20public-DraftStyleDefault-ltr%22%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FDIV%3E%3CDIV%20class%3D%22%22%3E%3CDIV%20class%3D%22public-DraftStyleDefault-block%20public-DraftStyleDefault-ltr%22%3E%3CSPAN%3ECreate%20and%20Approve%20a%20Requisition%20--%26gt%3B%20Create%20and%20Evaluate%20RFQ%20--%26gt%3B%20Create%2C%20Issue%20and%20Deliver%20Purchase%20Order%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%20class%3D%22public-DraftStyleDefault-block%20public-DraftStyleDefault-ltr%22%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FDIV%3E%3CDIV%20class%3D%22%22%3E%3CDIV%20class%3D%22public-DraftStyleDefault-block%20public-DraftStyleDefault-ltr%22%3E%3CSPAN%3EThese%20documents%20are%20all%20managed%20as%20rows%20of%20separate%20SQL%20tables.%20Each%20document%20has%20a%20unique%207-digit%20number.%20What%20I'm%20trying%20to%20do%20is%20merge%20the%20PO%20and%20Requisition%20tables%20together%20to%20establish%20a%20work%20flow%20to%20asses%20the%20cycle%20time%20through%20the%20whole%20process.%20Fortunately%2C%20the%20PO%20table%20has%20a%20column%20for%20the%20Requisition%20where%20it%20originated%20from%20so%20on%20the%20surface%20this%20merge%20seemed%20straightforward.%20The%207-digit%20Requisition%20number%20in%20the%20PO%20table%20should%20be%20an%20exact%20match%20back%20to%20its%20original%20entry%20in%20the%20Requisition%20table.%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%20class%3D%22public-DraftStyleDefault-block%20public-DraftStyleDefault-ltr%22%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FDIV%3E%3CDIV%20class%3D%22%22%3E%3CDIV%20class%3D%22public-DraftStyleDefault-block%20public-DraftStyleDefault-ltr%22%3E%3CSPAN%3EWhen%20I%20attempted%20this%20merge%2C%20however%2C%20I%20noticed%20that%20some%20of%20the%20POs%20are%20created%20using%20%3C%2FSPAN%3E%3CU%3Emultiple%20%3C%2FU%3E%3CSPAN%3ERequisitions%20bundled%20together%20so%20the%20value%20in%20the%20PO%20table%20may%20appear%20as%20%221234567%2C%201234568%22%20and%20so%20on.%20When%20this%20is%20the%20case%2C%20the%20Merge%20function%20is%20unable%20to%20recognize%20this%20and%20for%20a%20given%20Requisition%2C%20there%20is%20apparently%20no%20PO%20listed.%20Is%20there%20any%20way%20to%20allow%20the%20merge%20to%20%22see%22%20the%20correct%20Requisition%20among%20a%20comma-separated%20list%20in%20the%20PO%20table%3F%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%20class%3D%22public-DraftStyleDefault-block%20public-DraftStyleDefault-ltr%22%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FDIV%3E%3CDIV%20class%3D%22%22%3E%3CDIV%20class%3D%22public-DraftStyleDefault-block%20public-DraftStyleDefault-ltr%22%3E%3CSPAN%3ELet%20me%20know%20if%20any%20of%20this%20is%20unclear.%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CDIV%20class%3D%22%22%3E%3CDIV%20class%3D%22public-DraftStyleDefault-block%20public-DraftStyleDefault-ltr%22%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CDIV%20class%3D%22%22%3E%3CDIV%20class%3D%22public-DraftStyleDefault-block%20public-DraftStyleDefault-ltr%22%3E%3CSPAN%3EThanks!%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-973686%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-977763%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Merge%20Problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-977763%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F420870%22%20target%3D%22_blank%22%3E%40Anonymous5%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20need%20to%20have%20ID%3As%20in%20separate%20rows%20to%20merge.%20To%20do%20that%20add%20new%20column%20splitting%20your%20texts%20with%20comma%20separated%20ID%3As%20into%20the%20lists%20in%20that%20column%2C%20after%20that%20expand%20it%20and%20do%20merging%20on%20this%20new%20column.%20Generated%20script%20looks%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E...%0A%20%20%20%20%2F%2Fbe%20sure%20the%20column%20has%20TEXT%20type%0A%20%20%20%20%23%22Changed%20Type%22%20%3D%20Table.TransformColumnTypes(%0A%20%20%20%20%20%20%20%20Source%2C%0A%20%20%20%20%20%20%20%20%7B%7B%22Requisitions%22%2C%20type%20text%7D%7D%0A%20%20%20%20)%2C%0A%0A%20%20%20%20%2F%2F%20Add%20custom%20column%20splitting%20text%20to%20list%0A%20%20%20%20%2F%2F%20using%20comma%20as%20delimeter%0A%20%20%20%20%23%22Added%20Custom%22%20%3D%20Table.AddColumn(%0A%20%20%20%20%20%20%20%20%23%22Changed%20Type%22%2C%0A%20%20%20%20%20%20%20%20%22Custom%22%2C%0A%20%20%20%20%20%20%20%20each%20Text.Split(%5BRequisitions%5D%2C%22%2C%22)%0A%20%20%20%20)%2C%0A%0A%20%20%20%20%2F%2F%20expand%20new%20cusom%20column%20to%20rows%0A%20%20%20%20%23%22Expanded%20Custom%22%20%3D%20Table.ExpandListColumn(%0A%20%20%20%20%20%20%20%20%23%22Added%20Custom%22%2C%0A%20%20%20%20%20%20%20%20%22Custom%22%0A%20%20%20%20)%2C%0A%0A%20%20%20%20%2F%2F%20just%20in%20case%20trim%20it%0A%20%20%20%20%23%22Trimmed%20Text%22%20%3D%20Table.TransformColumns(%0A%20%20%20%20%20%20%20%20%23%22Expanded%20Custom%22%2C%0A%20%20%20%20%20%20%20%20%7B%7B%22Custom%22%2C%20Text.Trim%2C%20type%20text%7D%7D%0A%20%20%20%20)%0A...%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3ESimple%20sample%20is%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Anonymous5
Occasional Contributor
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!
1 Reply

@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.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies