Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Need help in calculating standard deviation using custom column

Dear All,   I would like to calcuate standard devation on below data using Custom column, please help I'm new to Power BI tool. Data looks like below   Source Data: Site Code DAY_DATE DAYS...
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi Anonymous,

     

    Please try below Power Query.

    let
    Source = Excel.Workbook(File.Contents("C:\Users\xxxx\Desktop\Sample Data (Autosaved) (Autosaved).xlsx"), null, true),
    data_Sheet = Source{[Item="data",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(data_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Site", type text}, {"Code", type text}, {"DAY_DATE", type datetime}, {"DAYS", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Site", "Code"}, {{"standard deviation", each List.StandardDeviation([DAYS]), type number}})
in
    #"Grouped Rows"

     

    Best regards,

    Yuliana Gu

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Thanks Tom & Stachu for your reply, I want same kind of logic to be done under query editor --> Custom Column because I need that result in a new column so that i can build rest of logic on top of it.

 

Stachu - It's actual standard devation data source & Expected output which is implemented in diff tool which we are migrating in power bi.

Stachu's avatar
Stachu
Community Champion
8 years ago

so you want to group the table with a new column calculating stadard deviation?

one more question - is the output you showed based on exacly same dataset that you posted? When I calculate the standard deviation I get the same results as the ones that TomMartens has posted, which are not consistent with your outcome (see YP site)

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Yes I want to group the table with a new column for calcuating standard deviation.

     

    Dear Stachu / Tom please help me.