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 eac...
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:

     

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

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

    i can share the excel with the dataset with you.
    How do i do it?

     

    • 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 

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

        I have it on Google drive but need your email to share with you