Data Summary - table with mutliple columns

G_Whit-UK 

Please add the following Code to a Blank Query in the Advanced Editor and check the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYicgVsCKY3WilYyQRAyNjMGkEVaVxlDzXLGYZWCub2igb2RgZADimME5IH0mOO1HNt0UScTUyBBMGmNVaQblheM2LxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Transaction ID" = _t, #"My Data Field 1" = _t, #"Counterpart Data Field 1" = _t, #"My Data Field 2" = _t, #"Counterpart Data Field 2" = _t, #"My Data Field 3" = _t, #"Counterpart Data Field 3" = _t, #"My Data Field 4" = _t, #"Counterpart Data Field 4" = _t]),
    #"Demoted Headers" = Table.DemoteHeaders(Source),
    #"Transposed Table" = Table.Transpose(#"Demoted Headers"),
    #"Filtered Rows" = Table.SelectRows(#"Transposed Table", each Text.StartsWith([Column1], "Counterpart")),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"Column1"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns"," ",null,Replacer.ReplaceValue,{"Value"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each if [Value] = null then 0 else 1),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Value"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns1",{{"Custom", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column1"}, {{"Count", each List.Sum([Custom]), type nullable number}})
in
    #"Grouped Rows"

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

Here is the modified version of Fowmy's code.  This solves the issue where Field 1 might have data and Counterpart Data be blank.  This was achieved by first merging the data fields before continuing with the suggested solution.  Perhaps this is not the most elegant solution given that there are nearly 100 merged fields in the actual data set - which creates a large number of steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYicgVsCKY3WilYyQRAyNjMGkEVaVxlDzXLGYZWCub2igb2RgZADimME5IH0mOO1HNt0UScTUyBBMGmNVaQblheM2LxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Transaction ID" = _t, #"My Data Field 1" = _t, #"Counterpart Data Field 1" = _t, #"My Data Field 2" = _t, #"Counterpart Data Field 2" = _t, #"My Data Field 3" = _t, #"Counterpart Data Field 3" = _t, #"My Data Field 4" = _t, #"Counterpart Data Field 4" = _t]),
    #"Merged Columns" = Table.CombineColumns(Source,{"My Data Field 1", "Counterpart Data Field 1"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Field 1"),
    #"Merged Columns1" = Table.CombineColumns(#"Merged Columns",{"My Data Field 2", "Counterpart Data Field 2"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Field 2"),
    #"Merged Columns2" = Table.CombineColumns(#"Merged Columns1",{"My Data Field 3", "Counterpart Data Field 3"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Field 3"),
    #"Merged Columns3" = Table.CombineColumns(#"Merged Columns2",{"My Data Field 4", "Counterpart Data Field 4"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Field 4"),
    #"Demoted Headers" = Table.DemoteHeaders(#"Merged Columns3"),
    #"Changed Type" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type"),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Transposed Table", {"Column1"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns"," ; ",null,Replacer.ReplaceValue,{"Value"}),
    #"Added Conditional Column" = Table.AddColumn(#"Replaced Value", "Breaks", each if [Value] = null then 0 else 1),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Conditional Column",{"Value"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns1", {"Column1"}, {{"Count", each List.Sum([Breaks]), type number}})
in
    #"Grouped Rows"