M Language (Excel 2016 Get and Transfom) Add several columns with totals RRS feed

  • Question

  • The data source has columns 1 to 25. I need to insert several Totals columns each adding the values of 5 columns (Total1 = Col1+Col6+Col11+Col16+Col21; Total2 = Col2+Col7+Col12+Col17+Col22; .........;Total5 = Col5+Col10+Col15+Col20+Col25)

    Other than manually adding five lines of Table.AddColumn, one for each total, is there a way to use a single line command? the real case consists of 375 columns, not 25.

    Thanks in advance;

    Friday, September 23, 2016 11:58 AM


  • This looks a bit unusual in my eyes, but it's perfectly doable. Just paste the following code into the advanced editor and follow the steps along.

    Then replace the Source-step by the reference to your data:

        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7c2xEYAwDEPRXVyniCXDsUsu+6+Bf4agopAKvUJrhWJEde7O08lJsSZzsicgQIAAAQIMGDBgwEABdQ6AAgq4ZuzxX397vV8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Col1 = _t, Col2 = _t, Col3 = _t, Col4 = _t, Col5 = _t, Col6 = _t, Col7 = _t, Col8 = _t, Col9 = _t, Col10 = _t, Col11 = _t, Col12 = _t, Col13 = _t, Col14 = _t, Col15 = _t, Col16 = _t, Col17 = _t, Col18 = _t, Col19 = _t, Col20 = _t, Col21 = _t, Col22 = _t, Col23 = _t, Col24 = _t, Col25 = _t]),
        AddIndex = Table.AddIndexColumn(Source, "RowNumber", 1, 1),
        #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(AddIndex, {"RowNumber"}, "Attribute", "Value"),
        #"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Value", type number}}),
        #"Split Column by Position" = Table.SplitColumn(#"Changed Type1","Attribute",Splitter.SplitTextByPositions({0, 3}, false),{"Attribute.1", "Attribute.2"}),
        #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Position",{{"Attribute.1", type text}, {"Attribute.2", Int64.Type}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "Bucket", each Number.RoundUp([Attribute.2]/5)*5),
        #"Grouped Rows" = Table.Group(#"Added Custom", {"RowNumber", "Bucket", "Attribute.1"}, {{"SumPerBucket", each List.Sum([Value]), type number}}),
        #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Grouped Rows", {{"Bucket", type text}}, "de-DE"),{"Attribute.1", "Bucket"},Combiner.CombineTextByDelimiter("Sum", QuoteStyle.None),"Bucket.1"),
        #"Merged Queries" = Table.NestedJoin(AddIndex,{"RowNumber"},#"Merged Columns",{"RowNumber"},"NewColumn",JoinKind.LeftOuter),
        #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Bucket.1", "SumPerBucket"}, {"Bucket.1", "SumPerBucket"}),
        #"Pivoted Column" = Table.Pivot(#"Expanded NewColumn", List.Distinct(#"Expanded NewColumn"[Bucket.1]), "Bucket.1", "SumPerBucket")
        #"Pivoted Column"

    Imke Feldmann

    Friday, September 23, 2016 2:04 PM