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

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

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

      also, the data is grouped by opportunity, and the nulls mean that the opportunity didnt go through the stage (column). Therefore, it shouldnt be taken into account..

       

      If null, then dont count the value in the average calculation.