Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
danielcontente
Helper II
Helper II

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
Super User

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
Super User

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

 

Amazing.

 

Thank you so much!

lbendlin
Super User
Super User

OneDrive, Dropbox, GoogleDrive ?

lbendlin
Super User
Super User

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.