Forum Discussion
Solved
Find Difference, Mean and Std dev. of comma separated values
Dear Community,
I am trying to find the difference between non-zero entities of 'Output' column (which is a vector value of size 8 and is in comma separated format). Also I would like to calculate mean and standard deviation for the resulting 'Difference' column if size is more than 1.
| Output | Difference | Mean | Std Dev. |
| 37,28,4,0,0,0,0,0 | 9,24 | 16.5 | 10.61 |
| 54,50,33,2,1,0,0,0 | 4,17,31,1 | 13.25 | 13.72 |
| 0,0,0,0,0,0,0,0 | 0 | 0 | 0.00 |
| 2884,0,0,0,0,0,0,0 | 0 | 0 | 0 |
| 500,450,420,385,370,300,150,50 | 50,30,35,15,70,150,100 | 64.28 | 47.12 |
E.g,
- [37,28,4,0,0,0,0,0] -> [(37-28), (28-4)] -> [9, 24]. Ignore calculating the difference if the value is zero.
- [54,50,33,2,1,0,0,0] -> [(54-50), (50-33), (33-2), (2-1)] -> [4,17,31,1]
Would really appreciate your help on this.
Thanks in Advance!
Hi DreamToGet ,
Please try:
First add index to the table:
Then duplicate your original table and split it into rows and add index
Here is the M code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY5rCgMhDISvUvw9hDyNPcvi/a/RuLZLH9REhoxfGI+jWUIHHPyqhnaHeol0iiVMXdrE0cIRDDMo5IIdkjCBLNRIY2vqucL4qHq7LjGfiI7h/6kdzAyvaNeKHwHL0vKkvODb96m99c/qKAS5QeEfssDupOPd8CTR5zTnAw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Output = _t, Difference = _t, #"Mean " = _t, #"Std Dev." = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Output", type text}, {"Difference", type text}, {"Mean ", type number}, {"Std Dev.", type number}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Difference", "Mean ", "Std Dev."}), #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Removed Columns", {{"Output", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Output"), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Output", Int64.Type}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 1, 1, Int64.Type) in #"Added Index"Output:
Then add column:
Difference = VAR _a = FILTER ( 'Table (2)', 'Table (2)'[Index] >= ( 'Table'[Index] * 8 - 7 ) && 'Table (2)'[Index] <= 'Table'[Index] * 8 ) VAR _b = ADDCOLUMNS ( _a, "Diff", IF ( MAXX ( FILTER ( _a, [Index] = EARLIER ( 'Table (2)'[Index] ) + 1 ), [Output] ) = 0, 0, [Output] - MAXX ( FILTER ( _a, [Index] = EARLIER ( 'Table (2)'[Index] ) + 1 ), [Output] ) ) ) VAR _c = CONCATENATEX ( FILTER ( _b, [Diff] <> 0 ), [Diff], "," ) RETURN IF ( MAXX ( _b, [Diff] ) = 0, "0", _c ) Mean = VAR _a = FILTER ( 'Table (2)', 'Table (2)'[Index] >= ( 'Table'[Index] * 8 - 7 ) && 'Table (2)'[Index] <= 'Table'[Index] * 8 ) VAR _b = ADDCOLUMNS ( _a, "Diff", IF ( MAXX ( FILTER ( _a, [Index] = EARLIER ( 'Table (2)'[Index] ) + 1 ), [Output] ) = 0, 0, [Output] - MAXX ( FILTER ( _a, [Index] = EARLIER ( 'Table (2)'[Index] ) + 1 ), [Output] ) ) ) VAR _c = AVERAGEX ( FILTER ( _b, [Diff] <> 0 ), [Diff] ) RETURN IF ( ISBLANK ( _c ), 0, _c ) Std.DEV = VAR _a = FILTER ( 'Table (2)', 'Table (2)'[Index] >= ( 'Table'[Index] * 8 - 7 ) && 'Table (2)'[Index] <= 'Table'[Index] * 8 ) VAR _b = ADDCOLUMNS ( _a, "Diff", IF ( MAXX ( FILTER ( _a, [Index] = EARLIER ( 'Table (2)'[Index] ) + 1 ), [Output] ) = 0, 0, [Output] - MAXX ( FILTER ( _a, [Index] = EARLIER ( 'Table (2)'[Index] ) + 1 ), [Output] ) ) ) VAR _c = STDEVX.S ( FILTER ( _b, [Diff] <> 0 ), [Diff] ) RETURN IF ( ISBLANK ( _c ), 0, _c )Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
v-jianboli-msftCommunity Support
Hi DreamToGet ,
Please try:
First add index to the table:
Then duplicate your original table and split it into rows and add index
Here is the M code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY5rCgMhDISvUvw9hDyNPcvi/a/RuLZLH9REhoxfGI+jWUIHHPyqhnaHeol0iiVMXdrE0cIRDDMo5IIdkjCBLNRIY2vqucL4qHq7LjGfiI7h/6kdzAyvaNeKHwHL0vKkvODb96m99c/qKAS5QeEfssDupOPd8CTR5zTnAw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Output = _t, Difference = _t, #"Mean " = _t, #"Std Dev." = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Output", type text}, {"Difference", type text}, {"Mean ", type number}, {"Std Dev.", type number}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Difference", "Mean ", "Std Dev."}), #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Removed Columns", {{"Output", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Output"), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Output", Int64.Type}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 1, 1, Int64.Type) in #"Added Index"Output:
Then add column:
Difference = VAR _a = FILTER ( 'Table (2)', 'Table (2)'[Index] >= ( 'Table'[Index] * 8 - 7 ) && 'Table (2)'[Index] <= 'Table'[Index] * 8 ) VAR _b = ADDCOLUMNS ( _a, "Diff", IF ( MAXX ( FILTER ( _a, [Index] = EARLIER ( 'Table (2)'[Index] ) + 1 ), [Output] ) = 0, 0, [Output] - MAXX ( FILTER ( _a, [Index] = EARLIER ( 'Table (2)'[Index] ) + 1 ), [Output] ) ) ) VAR _c = CONCATENATEX ( FILTER ( _b, [Diff] <> 0 ), [Diff], "," ) RETURN IF ( MAXX ( _b, [Diff] ) = 0, "0", _c ) Mean = VAR _a = FILTER ( 'Table (2)', 'Table (2)'[Index] >= ( 'Table'[Index] * 8 - 7 ) && 'Table (2)'[Index] <= 'Table'[Index] * 8 ) VAR _b = ADDCOLUMNS ( _a, "Diff", IF ( MAXX ( FILTER ( _a, [Index] = EARLIER ( 'Table (2)'[Index] ) + 1 ), [Output] ) = 0, 0, [Output] - MAXX ( FILTER ( _a, [Index] = EARLIER ( 'Table (2)'[Index] ) + 1 ), [Output] ) ) ) VAR _c = AVERAGEX ( FILTER ( _b, [Diff] <> 0 ), [Diff] ) RETURN IF ( ISBLANK ( _c ), 0, _c ) Std.DEV = VAR _a = FILTER ( 'Table (2)', 'Table (2)'[Index] >= ( 'Table'[Index] * 8 - 7 ) && 'Table (2)'[Index] <= 'Table'[Index] * 8 ) VAR _b = ADDCOLUMNS ( _a, "Diff", IF ( MAXX ( FILTER ( _a, [Index] = EARLIER ( 'Table (2)'[Index] ) + 1 ), [Output] ) = 0, 0, [Output] - MAXX ( FILTER ( _a, [Index] = EARLIER ( 'Table (2)'[Index] ) + 1 ), [Output] ) ) ) VAR _c = STDEVX.S ( FILTER ( _b, [Diff] <> 0 ), [Diff] ) RETURN IF ( ISBLANK ( _c ), 0, _c )Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.