+ Reply to Thread
Results 1 to 4 of 4

Power Query extraction of field in duplicated record

  1. #1
    Registered User
    Join Date
    08-29-2018
    Location
    Melbourne, Australia
    MS-Off Ver
    2016/365 both
    Posts
    22

    Power Query extraction of field in duplicated record

    I have many queries attached to an SQL Power Query. I would love some help with the following for a Job Position Dashboard.

    Some staff have duplicated job positions, either because they are in leadership positions or because they are teachers on leave. I would like to deduct the fte of staff on leave so I can have an accurate total FTE at any point in time. However, I'm not sure how to extract only those staff on leave (Parental/ LSL (Long Service Leave)/ Long term Carers Leave) as opposed to those who also have duplicate records due to their leadership or double roles e.g. some teachers are also Education Support officers.

    I am thinking of a if then else statement but don't know how to do this with rows. I have attached an example of the file and the output which gives zero FTE. I would like this to show their FTE e.g. Joanna Bonner should show 1 as that is her usual FTE when not on LSL.

    Thank you for any assistance.
    Attached Files Attached Files

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Power Query extraction of field in duplicated record

    I'm not sure what you want.

    Maybe try this

    Please Login or Register  to view this content.
    You may change Max to Sum if needed.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    08-29-2018
    Location
    Melbourne, Australia
    MS-Off Ver
    2016/365 both
    Posts
    22

    Re: Power Query extraction of field in duplicated record

    Thank you Bo-Ry. It is exactly what I want and works perfectly. This will help our HR Department with a Job Position Dashboard.

  4. #4
    Registered User
    Join Date
    08-29-2018
    Location
    Melbourne, Australia
    MS-Off Ver
    2016/365 both
    Posts
    22

    Re: Power Query extraction of field in duplicated record

    Good Morning Bo-Ry
    I know I said it works, which it does. However, I'm having trouble with the coding in the "real" workbook. Any chance you can scan it for my error: Expression Error: The column 'ID' of the table wasn't found. Details: ID.

    I found the solution by : changing the Source to be the original pvtable name and the FTE to be the "real" column not the calculated column (in layman's terms). Now it works. Thank you.
    Attached Files Attached Files
    Last edited by Stefj; 05-11-2020 at 10:48 PM. Reason: Completed

+ 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. [SOLVED] Power Query - excel formula translation into Power Query
    By afgi in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 02-19-2020, 03:38 AM
  2. Replies: 4
    Last Post: 02-17-2020, 06:03 AM
  3. Replies: 3
    Last Post: 11-06-2019, 10:45 AM
  4. [SOLVED] Store Power Query table and add calculated field
    By jaryszek in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 02-01-2019, 08:18 AM
  5. Power Query: Check if Field contains text from list
    By learning new things in forum Excel General
    Replies: 0
    Last Post: 11-07-2018, 03:33 PM
  6. Sub-Forum for Excel Power Tools (Power Query, Power Pivot & Power BI)
    By chullan88 in forum Suggestions for Improvement
    Replies: 10
    Last Post: 06-28-2018, 02:25 PM
  7. Record extraction using VBA and web query
    By doublea17 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-14-2014, 06:40 AM

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