Forum Discussion

AmaGreen's avatar
AmaGreen
Frequent Visitor
2 years ago

Descriptive Statistics on multiple columns

Hello Community, I have a dataset that I need to perform calculate the average, minimum and maxim based on the specific categories of items.  I would like an assistance on how to perform the descri...
Need Help
AmaGreen's avatar
AmaGreen
Frequent Visitor
2 years ago

Hello Anonymous 

Thank you for the response and suggestion. 

Please can you write an illustrative example to help me understand your solution better? Alternatively, if there is a resource you can direct me to, I would greatly appreciate it .

Looking forward to your further assistance.

 

Thanks.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

HI AmaGreen,

I find a simple way to do these, you can merge these columns of groups and unpivot and spilt these columns to convert these records to suitable table format.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY47EoAgDESv4lBTSPhpKXoLhvtfwxgWDWOxRXb3JanVOGPNwSLWyQqsRdRsFbfAvVijHSX1mBxaNLFBbSyKJUkj0o/RbAKT3nu97SXNiv1v3vBrhuvR6uyumPn+k7pVQeOlXm/tBg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Individual = _t, #"Item name (Day 1)" = _t, #"Item quantity" = _t, #"Item name (Day 2)" = _t, #"Item quantity.1" = _t, #"Item name (Day 3)" = _t, #"Item quantity.2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Individual", Int64.Type}, {"Item name (Day 1)", type text}, {"Item quantity", Int64.Type}, {"Item name (Day 2)", type text}, {"Item quantity.1", Int64.Type}, {"Item name (Day 3)", type text}, {"Item quantity.2", Int64.Type}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"Item quantity", type text}}, "en-US"),{"Item name (Day 1)", "Item quantity"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"1"),
    #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns", {{"Item quantity.1", type text}}, "en-US"),{"Item name (Day 2)", "Item quantity.1"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"2"),
    #"Merged Columns2" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns1", {{"Item quantity.2", type text}}, "en-US"),{"Item name (Day 3)", "Item quantity.2"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"3"),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Merged Columns2", {"Individual"}, "Day", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Value", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Attribute", "Value"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute", type text}, {"Value", Int64.Type}})
in
    #"Changed Type1"

Then you can write Dax expression to simply compare between these fields instead of calculate across multiple table fields.

Regards,

Xiaoxin Sheng