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
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    49,794

    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!

    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.

  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,794

    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
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    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
    sandy
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Tips for Excellent Spreadsheets

    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  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,794

    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