Closed Thread
Results 1 to 5 of 5

How to Correctly filter data from data dump into a condensed table set VBA or PQ??

  1. #1
    Registered User
    Join Date
    12-05-2017
    Location
    Glasgow,Scotland
    MS-Off Ver
    2014
    Posts
    15

    Arrow How to Correctly filter data from data dump into a condensed table set VBA or PQ??

    Hi all,

    I have an issue of when trying to go through Power Query to filter through my data set into a more condensed format to implement into a table. Basically when I unpivot my data it displays it in the way I want it but the data is wrong and I'm struggling to get my head round it. Im looking to match the correct Milestone with its Milestone date Variance number . As of now when I filter through I get 3 different Unpivotted numbers to the one milestone when im looking to have one per milestone (If there is a value).

    I have included a simple example for reference . Any help or guidance would be a massive help


    Thanks in advance .
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: How to Correctly filter data from data dump into a condensed table set VBA or PQ??

    You are going to need to manually populate the results table so that we know what we are aiming for.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: How to Correctly filter data from data dump into a condensed table set VBA or PQ??

    Is this what you are looking for (you can rename the column headers)?

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    E
    F
    G
    H
    1
    Project ID Project Name Project Critical Senior Manager Project Manager Points Value.1 Value.2
    2
    5014180000
    Project 1 Yes Manager 1 Project Manager 1
    0.098833403
    14/05/2018
    0
    3
    5014180000
    Project 1 Yes Manager 1 Project Manager 1
    0.098833403
    30/04/2018
    0
    4
    5014180000
    Project 1 Yes Manager 1 Project Manager 1
    0.098833403
    03/09/2018
    0
    5
    5014180000
    Project 1 Yes Manager 1 Project Manager 1
    0.098833403
    02/10/2018
    0
    6
    5014180000
    Project 1 Yes Manager 1 Project Manager 1
    0.098833403
    13/05/2019
    0
    7
    5014180000
    Project 1 Yes Manager 1 Project Manager 1
    0.098833403
    05/08/2019
    0
    8
    5014180000
    Project 1 Yes Manager 1 Project Manager 1
    0.098833403
    04/12/2019
    0
    9
    5014180000
    Project 1 Yes Manager 1 Project Manager 1
    0.098833403
    02/06/2021
    0
    10
    5011590000
    Project 2 No Manager 2 Project Manager 2
    0
    02/07/2018
    0
    11
    5011590000
    Project 2 No Manager 2 Project Manager 2
    0
    18/06/2018
    -4
    12
    5011590000
    Project 2 No Manager 2 Project Manager 2
    0
    11/02/2019
    -4
    13
    5011590000
    Project 2 No Manager 2 Project Manager 2
    0
    03/07/2019
    -3.857142857
    14
    5011590000
    Project 2 No Manager 2 Project Manager 2
    0
    23/09/2020
    -4
    15
    5011590000
    Project 2 No Manager 2 Project Manager 2
    0
    23/12/2020
    -4
    16
    5011590000
    Project 2 No Manager 2 Project Manager 2
    0
    13/01/2021
    -4
    17
    5011590000
    Project 2 No Manager 2 Project Manager 2
    0
    21/09/2022
    -4
    18
    5012900000
    Project 3 No Manager 1 Project Manager 2
    0
    16/04/2018
    0
    19
    5012900000
    Project 3 No Manager 1 Project Manager 2
    0
    24/12/2015
    0
    20
    5012900000
    Project 3 No Manager 1 Project Manager 2
    0
    16/04/2018
    0
    Sheet: Sheet3

    If so, have a look at the attached, which uses this M code in the PowerQuery and merges columns before pivoting them:

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by AliGW; 03-18-2018 at 12:46 PM.

  4. #4
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: How to Correctly filter data from data dump into a condensed table set VBA or PQ??

    Two different accounts with exactly the same attachment and problem?
    https://www.excelforum.com/excel-gen...able-help.html

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: How to Correctly filter data from data dump into a condensed table set VBA or PQ??

    Naughty!!!

    Continue here, please: https://www.excelforum.com/excel-gen...able-help.html

    Duplicate thread closed.

Closed Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Sorting data dump for a pivot table
    By scottylanes in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 06-01-2016, 05:53 PM
  2. [SOLVED] VBA needed to extract specific data from data dump list
    By stinkyd in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-13-2015, 06:40 PM
  3. Pivot table not displaying data correctly
    By Klipvis in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 10-23-2014, 06:24 AM
  4. Replies: 9
    Last Post: 05-20-2014, 07:52 PM
  5. Multiple tabs with dynamic data condensed by formula to one tab
    By mhgleitz in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-18-2012, 09:30 PM
  6. How To Filter This Data Correctly?
    By steveori in forum Excel General
    Replies: 4
    Last Post: 01-20-2012, 01:09 PM
  7. Mulitple Time stamp data to be condensed
    By sajeel in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-20-2011, 04:47 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