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

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

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.