SOLVED
Home

Merge Question Between Data Tables with Shared Column

%3CLINGO-SUB%20id%3D%22lingo-sub-895583%22%20slang%3D%22en-US%22%3EMerge%20Question%20Between%20Data%20Tables%20with%20Shared%20Column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-895583%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Everyone%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20fairly%20new%20to%20Excel%20Power%20Query%20but%20I%20am%20really%20liking%20this%20tool%20and%20what%20it%20can%20do.%20I%20am%20currently%20using%20it%20to%20access%20an%20SQL%20database.%20I%20am%20familiar%20with%20how%20to%20Merge%20queries%20of%20two%20different%20tables%20around%20a%20shared%20column.%20My%20question%20is%20regarding%20something%20I%20haven't%20encountered%20up%20to%20now.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20be%20more%20specific%20my%20company%20uses%20a%20maintenance%20tracking%20program%20that%20is%20backed%20up%20with%20the%20SQL%20table.%20I%20can%20pretty%20easily%20access%20the%20table%20I%20need%20to%20get%20basic%20information%20about%20a%20particular%20job%20(%22WO%22%20for%20Work%20Order).%20There%20are%20a%20couple%20fields%20I%20am%20after%20though%20that%20involve%20written%20descriptions%20of%20the%20problem%20and%20work%20done%20to%20repair%20the%20issue.%20This%20is%20represented%20in%20the%20first%20table%20by%20a%20unique%20identifier.%20These%20terms%20are%20a%209-digit%20number%20and%20they%20are%20respectively%20called%20%22Description_ID%22%20and%20%22Findings_ID%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20obtain%20the%20actual%20text%20data%2C%20I%20need%20to%20head%20over%20to%20a%20separate%20table%20called%20%22WO_Remarks%22.%20It%20is%20a%202-column%20data%20table%20with%20a%209-digit%20number%20in%20the%20first%20column%20and%20the%20actual%20raw%20text%20in%20the%20second.%20As%20it%20turns%20out%2C%20this%20table%20has%20combined%20both%20%22Description_ID%22%20and%20%22Findings_ID%22%20into%20a%20single%20column%20all%20mixed%20together.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20question%20is%2C%20is%20there%20a%20way%20for%20Power%20Query%20to%20parse%20out%20the%20text%20entries%20for%20each%20of%20the%20columns%20in%20the%20first%20table.%20Ideally%20I'd%20like%20to%20merge%20them%20so%20that%20the%20larger%20and%20more%20descriptive%20%22WO%22%20table%20would%20include%20the%20%22description%22%20and%20%22findings%22%20text%20for%20reporting%20purposes.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELet%20me%20know%20if%20any%20of%20this%20is%20unclear%20and%20I'd%20be%20happy%20to%20provide%20some%20screen%20shots.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERgds%2C%3C%2FP%3E%3CP%3ERob%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-895583%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EPower%20Query%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-895825%22%20slang%3D%22en-US%22%3ERe%3A%20Merge%20Question%20Between%20Data%20Tables%20with%20Shared%20Column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-895825%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%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHi%20Rob%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYes%2C%20it's%20better%20with%20screenshort%20or%20small%20sample%20which%20illustrates%20data%20logic.%20Like%20this%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20371px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F135619iD60919E0A8906C40%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-896040%22%20slang%3D%22en-US%22%3ERe%3A%20Merge%20Question%20Between%20Data%20Tables%20with%20Shared%20Column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-896040%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Sergei%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20reply.%20To%20clarify%20things%2C%20please%20see%20a%20screenshot%20of%20the%20first%20table%20named%20%22WO%22%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F135628iFE2027004E528C2A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22WO_Table.jpg%22%20title%3D%22WO_Table.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20you%20can%20see%20there%20are%20two%20columns%20%22WO_Description_ID%22%20and%20%22Findings_ID%22.%20There%20are%20for%20the%20most%20part%20sequential%20but%20the%20same%20numbering%20scheme.%20Each%20row%20is%20associated%20with%20a%20unique%20work%20order.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20second%20table%20below%20is%20in%20the%20same%20database%20and%20titled%20%22WO_Remarks%22%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20731px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F135629i2CE097EBF6B373E2%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Remarks%20Table.jpg%22%20title%3D%22Remarks%20Table.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThis%20has%20the%20first%20column%20where%20the%20%22description%20ID%22%20and%20%22findings%20ID%22%20from%20above%20have%20all%20been%20collectively%20dumped%20in%20a%20single%20column.%20I%20am%20after%20the%20content%20of%20the%20%22remarks%22%20column%20above%20but%20need%20to%20extract%20this%20text%20using%20the%20uniqe%20%22ID%22%20identifier.%20Since%20it%20is%20done%20this%20way%2C%20every%20row%20of%20the%20first%20table%20will%20correspond%20to%20two%20rows%20of%20the%20second%20table%20(one%20row%20for%20description%20and%20one%20for%20findings%20remark%20text).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20can%20I%20extract%20this%20data%20from%20the%20second%20table%20and%20tie%20to%20the%20correct%20individual%20row%20of%20the%20first%20table%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%2C%3C%2FP%3E%3CP%3ERob%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-897620%22%20slang%3D%22en-US%22%3ERe%3A%20Merge%20Question%20Between%20Data%20Tables%20with%20Shared%20Column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-897620%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%3ERob%2C%20you%20may%20merge%20your%20first%20table%20with%20second%20one%20on%20WO_Description_ID%20and%20extract%20Remarks%20column.%20After%20that%20merge%20resulting%20table%20with%20second%20one%20on%20FINDINGS_ID%20and%20extract%20remarks%20as%20Remarks.1%20column.%3C%2FP%3E%0A%3CP%3ESelect%20Remarks%20and%20Remarks.1%20columns%2C%20on%20Power%20Query%20editor%20ribbon%20Add%20Column-%26gt%3BMerge%20Columns%2C%20merge%20them%20with%20custom%20line%20feed%20separator%20%23(lf).%20Or%20any%20one%20which%20you%20prefer.%20All%20remarks%20will%20be%20in%20resulting%20column.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-898229%22%20slang%3D%22en-US%22%3ERe%3A%20Merge%20Question%20Between%20Data%20Tables%20with%20Shared%20Column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-898229%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20solved%20my%20issue.%20It%20seems%20quite%20obvious%20what%20I%20needed%20to%20do%20now.%20Worked%20great.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERgds%2C%3C%2FP%3E%3CP%3ERob%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-899766%22%20slang%3D%22en-US%22%3ERe%3A%20Merge%20Question%20Between%20Data%20Tables%20with%20Shared%20Column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-899766%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%2C%20you%20are%20welcome%2C%20glad%20to%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Anonymous5
Occasional Contributor

Hello Everyone,

 

I am fairly new to Excel Power Query but I am really liking this tool and what it can do. I am currently using it to access an SQL database. I am familiar with how to Merge queries of two different tables around a shared column. My question is regarding something I haven't encountered up to now.

 

To be more specific my company uses a maintenance tracking program that is backed up with the SQL table. I can pretty easily access the table I need to get basic information about a particular job ("WO" for Work Order). There are a couple fields I am after though that involve written descriptions of the problem and work done to repair the issue. This is represented in the first table by a unique identifier. These terms are a 9-digit number and they are respectively called "Description_ID" and "Findings_ID".

 

To obtain the actual text data, I need to head over to a separate table called "WO_Remarks". It is a 2-column data table with a 9-digit number in the first column and the actual raw text in the second. As it turns out, this table has combined both "Description_ID" and "Findings_ID" into a single column all mixed together.

 

My question is, is there a way for Power Query to parse out the text entries for each of the columns in the first table. Ideally I'd like to merge them so that the larger and more descriptive "WO" table would include the "description" and "findings" text for reporting purposes. 

 

Let me know if any of this is unclear and I'd be happy to provide some screen shots.

 

Rgds,

Rob

5 Replies
Highlighted

@Anonymous5 

 

Hi Rob,

 

Yes, it's better with screenshort or small sample which illustrates data logic. Like this

image.png

Highlighted

Hi Sergei,

 

Thanks for the reply. To clarify things, please see a screenshot of the first table named "WO":

WO_Table.jpg

 

As you can see there are two columns "WO_Description_ID" and "Findings_ID". There are for the most part sequential but the same numbering scheme. Each row is associated with a unique work order.

 

The second table below is in the same database and titled "WO_Remarks":

Remarks Table.jpg

This has the first column where the "description ID" and "findings ID" from above have all been collectively dumped in a single column. I am after the content of the "remarks" column above but need to extract this text using the uniqe "ID" identifier. Since it is done this way, every row of the first table will correspond to two rows of the second table (one row for description and one for findings remark text).

 

How can I extract this data from the second table and tie to the correct individual row of the first table?

 

Thank you,

Rob

 

 

Highlighted
Solution

@Anonymous5 

Rob, you may merge your first table with second one on WO_Description_ID and extract Remarks column. After that merge resulting table with second one on FINDINGS_ID and extract remarks as Remarks.1 column.

Select Remarks and Remarks.1 columns, on Power Query editor ribbon Add Column->Merge Columns, merge them with custom line feed separator #(lf). Or any one which you prefer. All remarks will be in resulting column.

Highlighted

Thanks @Sergei Baklan ,

 

This solved my issue. It seems quite obvious what I needed to do now. Worked great.

 

Rgds,

Rob

Highlighted

@Anonymous5 , you are welcome, glad to help