+ Reply to Thread
Results 1 to 10 of 10

Excel Query -Pulling in Translated Field Value

  1. #1
    Registered User
    Join Date
    05-01-2020
    Location
    USA
    MS-Off Ver
    2019
    Posts
    8

    Excel Query -Pulling in Translated Field Value

    I have an extensive Access background and am being "forced" to transition our databases to Excel. I'm trying to teach myself power query in Excel and am cringing over how much more effort it seems I'll have to go through to even create basic queries. As a hypothetical example, say I have two tables. One is a list of employees. That table lists the employee id, employee name, and their status code of A, L, or T. I also have a status table with the descriptions for A, L and T. A's description is Active, code L is Leave and T is Term. I have figured out how to add both tables to a data model in Excel and have related the status code on the table of employees to the status code on the the status code table. What I can't figure out how to do is how to bring in the description from the status table. Not sure what I'm missing and would appreciate any help. Thanks!
    Attached Files Attached Files
    Last edited by LisaWilliams231; 05-01-2020 at 01:20 PM.

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,726

    Re: Excel Query -Pulling in Translated Field Value

    Wait... are you transitioning DB from Access to Excel? Or just the reporting functions (i.e. data query & data modeling)?

    Data entry and storage should still be managed in Access. Excel & Power Query / Power Pivot should be used for modeling and analysis.

    PQ is the ETL tool and Power Pivot is for modeling and analysis.

    When building data model, almost always, data should be denormalized. From model optimized for data entry (CRUD) & storage to the one optimized for analysis and reporting.

    Typically, you'd create star schema (one fact table, with many connected dimension tables) or fact constellation schema (many fact table with some shared/common dimension tables).

    Have a read of link below. It's specifically for PowerBI, but underlying engine is same as PQ & Power Pivot.
    https://www.sqlbi.com/articles/the-i...s-in-power-bi/

    It isn't clear to me, what your current query and/or data model looks like. I'd recommend posting your entire M query code (copy paste from Advanced Editor pane).

    I'd also recommend including data model in your sample. Failing that, include diagram of data relationship.

    Edit: If you want single flat table as end result, I'd just use Data ->From Access -> Advanced. And use native Access query to bring in the data.
    Last edited by CK76; 05-01-2020 at 12:56 PM.
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    49,471

    Re: Excel Query -Pulling in Translated Field Value

    Welcome to the forum.

    What you do is merge the lookup table with the main table using the status code filed as the joining field.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  4. #4
    Registered User
    Join Date
    05-01-2020
    Location
    USA
    MS-Off Ver
    2019
    Posts
    8

    Re: Excel Query -Pulling in Translated Field Value

    Thanks for the reply. The business has had it with "rogue" databases and is pulling the plug, so I'm trying to figure out how to transition our reporting that's currently in Access to Excel. I've created a little spreadsheet with the two data tables and have created the table relationship and attached it to the original post. I suppose I could do a vlookup on the on the empl table against the values on the status code table, but was hoping to find an Excel query solution.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    49,471

    Re: Excel Query -Pulling in Translated Field Value

    Sorry - the attachment refers to data that isn't in the workbook, so it's not possible to help. You need all sample data in the one workbook.

  6. #6
    Registered User
    Join Date
    05-01-2020
    Location
    USA
    MS-Off Ver
    2019
    Posts
    8

    Re: Excel Query -Pulling in Translated Field Value

    Thanks for the reply, I posted the wrong file I just reposted the right one (hopefully)

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    49,471

    Re: Excel Query -Pulling in Translated Field Value

    I loaded the lookup table to connection only, then loaded the main table and merged it with the query I just created.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    05-01-2020
    Location
    USA
    MS-Off Ver
    2019
    Posts
    8

    Re: Excel Query -Pulling in Translated Field Value

    Got it!! You are an angel AliGW! Thanks!

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    49,471

    Re: Excel Query -Pulling in Translated Field Value

    You're welcome!

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  10. #10
    Registered User
    Join Date
    05-01-2020
    Location
    USA
    MS-Off Ver
    2019
    Posts
    8

    Re: Excel Query -Pulling in Translated Field Value

    Thanks again...looks like I have a lot to learn

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Power Query Sql Database Relationships
    By kersplash in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-28-2019, 07:42 AM
  2. Replies: 1
    Last Post: 02-27-2015, 05:23 PM
  3. Mail Merge - Excel Document - One field not pulling in
    By degross77 in forum Excel General
    Replies: 0
    Last Post: 08-30-2013, 02:14 PM
  4. Pulling a Query from Excel via Access
    By Masact in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-12-2009, 11:56 AM
  5. excel 2003 query criteria field value help
    By davidtla in forum Excel General
    Replies: 2
    Last Post: 10-20-2008, 10:10 AM
  6. excel 2003 having problems pulling top table from web query
    By dvpetta@yahoo.com in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-30-2005, 06:10 PM
  7. How do I create a query field in Excel
    By Sean in forum Excel General
    Replies: 1
    Last Post: 07-20-2005, 04:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1