cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
danielcontente
Helper I
Helper I

Merge columns and add new fields

Hi, 

 

I have the following dataset:

danielcontente_0-1627469792246.png

 

...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)

danielcontente_2-1627469891982.png

 

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!

 

1 ACCEPTED SOLUTION
lbendlin
Super User III
Super User III

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_0-1627595878909.png

 

View solution in original post

9 REPLIES 9
lbendlin
Super User III
Super User III

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_0-1627595878909.png

 

View solution in original post

Amazing.

 

Thank you so much!

lbendlin
Super User III
Super User III

OneDrive, Dropbox, GoogleDrive ?

lbendlin
Super User III
Super User III

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

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

 

Put it on OneDrive or Dropbox.

 

Note: averaging with missing data will result in unreliable output 

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.

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

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

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors