Forum Discussion

danielcontente's avatar
danielcontente
Icon for Helper II rankHelper II
5 years ago
Solved

Merge columns and add new fields

Hi, 

 

I have the following dataset:

 

...and i am trying to merge columns (from Initial Interest Conversion Time) untill PO this Quarter Conversion Time), so then i have the average of each of those columns, per stage, per day..

the end result should look like this more or less:

(i dont need to have all the dates in the calendar; only the dates in the Opportunity Created Date column in the illustration above)

 

the idea of doing this is to, when building a clustered bar chat, i can have the stages as a Legend with ther respective values.
So if someone have a better idea on how to get this illustration without doing a workaround with the data, it is also valid.

 

Thank you very very much!

 

Need Help
Show and Tell
Tips and Tricks
  • lbendlin's avatar
    lbendlin
    5 years ago

    First step: Unpivot your data

     

    let
    Source = Csv.Document(File.Contents("Acceleration Example.xlsx - Sheet3.csv"),[Delimiter=",", Columns=8, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Opportunity CreatedDate", type date}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Opportunity ID", "Opportunity CreatedDate"}, "Attribute", "Value"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Value", type number}})
in
    #"Changed Type1"

     

    Next, add the required fields to table visual and set aggregation for the value column to Average:

     

9 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    5 years ago

    have you decided how to handle null values in your average calculation?

    • danielcontente's avatar
      danielcontente
      Icon for Helper II rankHelper II
      5 years ago

      The NULLs means there was no activity related to this stage, and therefore shouldnt be counted; it is like it doesnt exist

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User
        5 years ago

        Put it on OneDrive or Dropbox.

         

        Note: averaging with missing data will result in unreliable output 

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    5 years ago

    First step: Unpivot your data

     

    let
    Source = Csv.Document(File.Contents("Acceleration Example.xlsx - Sheet3.csv"),[Delimiter=",", Columns=8, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Opportunity CreatedDate", type date}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Opportunity ID", "Opportunity CreatedDate"}, "Attribute", "Value"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Value", type number}})
in
    #"Changed Type1"

     

    Next, add the required fields to table visual and set aggregation for the value column to Average: