none
Create custom column in power query RRS feed

  • Question

  • Hello All,

    I have a requirement to create report for the Analysis, my raw data looks like below which I'm extracting from multiple tables. I would like to extract project wise number count  based on status, my idea is to create custom columns to write the value if the value matches to the condition else blank. Example my status has multiple status value ( closed, on-hold,warranty,inprogress,etc) i need to know what formula will help me extract data in different columns so that i can get the count in pivot for each status)


    NUMBER         CATEGORY                                Internal SLA       EXTERNALSLA      DATE_ENTERED    STATUS
    C1000001    HEINEKEN                                    t                               t                  9/13/2014            CLOSED
    C1000002    Migration-BLACKROCK AppV                                                            2/11/2015    Terminated
    C1000004    Migration-BLACKROCK AppV                                                           10/24/2014    CLOSED
    C1000005    Migration-BLACKROCK AppV        t                                  t                 1/12/2015    On-Hold
    C1000006    Migration-BLACKROCK AppV                                                            11/5/2014    CLOSED
    C1000007    Migration-BLACKROCK AppV    t                                        t               9/12/2014    CLOSED
    C1000008    Migration-BLACKROCK AppV                                                               8/6/2014    On-Hold
    C1000009    BLACKROCK                                                                                       8/5/2014    In Progress
    C1000010    Migration-BLACKROCK AppV    t                                          t                 8/5/2014    On-Hold
    C1000011    Migration-BLACKROCK AppV                                                               10/25/2014    CLOSED
    C1000012    BLACKROCK                                                                                         9/24/2014    CLOSED
    C1000013    BLACKROCK                                                                                       10/16/2014    Terminated
    C1000015    BLACKROCK                                                                                         9/1/2014    In Progress

    Monday, February 16, 2015 9:27 AM

Answers

  • Hi Imke,

    I have achieved what i wanted to... I did try the way you have suggested. But end result was not fruitful.

    I did following for status wise count.

    Step1: In power query I have created duplicate status column for each status available.( Example I have status {closed,open,initial... etc} I have created duplicate column for each status and replaced with null.For closed Except closed all i replaced in column with null).

    Step2: So every time my power query data refreshes it does that step each time so that i can get the count of each status column wise in my pivot table.

    I have attached the result,


    Harsha

    • Marked as answer by HarshaG123 Wednesday, March 4, 2015 5:03 AM
    Tuesday, March 3, 2015 5:15 AM
  • Hi Harsha,

    great you made it.

    Maybe you could mark your question as answered.


    Imke

    • Marked as answer by HarshaG123 Wednesday, March 4, 2015 5:03 AM
    Tuesday, March 3, 2015 6:09 PM
    Moderator

All replies

  • Hi HarshaG123. Can you provide an example of what the result should look like after the custom column is added?

    Thanks,

    Ehren

    Monday, February 16, 2015 6:27 PM
    Owner
  • Read this blog post by Ken Puls to learn how to add if-statements in custom columns.
    Monday, February 16, 2015 10:54 PM
  • Hi,

    I wanted to create column by extract the matching value of the type in corresponding row of the custom column.

    Example: I have a coulmn name  "status" which has {open,close,warranty}, i wanted to create Open as column,close as a column ,and warranty so on...!!

    I wanted to write condition which matches of type status write in respective column, else it should write null so that in pivot i can use for count.

    Tuesday, February 17, 2015 9:27 AM
  • Hi Ken,

    What if i want to match mutiple value to true condition and only one false condition. Is there any way?

    Tuesday, February 17, 2015 9:29 AM
  • Hi Harsha,

    how much would an ordinary Pivot table on this data differ from your desired result:

    If you put Status in Cols, Category in Rows and Number in the Value section with "Summarize value by" (in the Value Field Settings) set to "Count" instead of "Sum"?

    If you could paint your desired changes to this picture, maybe I can understand you better.

    Thanks and BR,

    Imke


    hth, Imke

    Tuesday, February 17, 2015 1:42 PM
    Moderator
  • Hi Imke,

    I have achieved what i wanted to... I did try the way you have suggested. But end result was not fruitful.

    I did following for status wise count.

    Step1: In power query I have created duplicate status column for each status available.( Example I have status {closed,open,initial... etc} I have created duplicate column for each status and replaced with null.For closed Except closed all i replaced in column with null).

    Step2: So every time my power query data refreshes it does that step each time so that i can get the count of each status column wise in my pivot table.

    I have attached the result,


    Harsha

    • Marked as answer by HarshaG123 Wednesday, March 4, 2015 5:03 AM
    Tuesday, March 3, 2015 5:15 AM
  • Hi Harsha,

    great you made it.

    Maybe you could mark your question as answered.


    Imke

    • Marked as answer by HarshaG123 Wednesday, March 4, 2015 5:03 AM
    Tuesday, March 3, 2015 6:09 PM
    Moderator