Data Summary - table with mutliple columns

[G_Whit-UK]

Hi

I'm attempting to find a solution within Power Query using a data file provided by one of our vendors (using a regualtor prescribed report format).  The file has a little over 200 columns and reflects the reconciliation output of my firm's data versus that of our multiple counterparts.  Below is an example of the file structure using dummy data:

Transaction ID	My Data Field 1	Counterpart Data Field 1	My Data Field 2	Counterpart Data Field 2	My Data Field 3	Counterpart Data Field 3	My Data Field 4	Counterpart Data Field 4
1	A	B
2			123	122
3	A	E					07/10/2020	06/10/2020
4
5			521	523
6		W

I would like to obtain a summary of the above in this format:

Field ID:	Count
Field 1:	3
Field 2:	2
Field 3:	0
Field 4:	1

I would prefer to find a Query Editor solution rather than DAX.

Does anyone know of a clever solution?

Thanks

[Fowmy · Accepted solution]

@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

[G_Whit-UK · Accepted solution]

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"

[Smauro]

Since you want to combine them, I'd suggest looking at this function here

And, using the solution I wrote earlier which skips heavy calculations like pivoting or grouping:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYicgRkexOtFKRnCuoZExmDTCUGMMNcMVIqYAB3BlBub6hgb6RgZGBiCOGZwD0m2CxWaE2aZwrqmRIZg0xlBjBmGHYzMiFgA=", 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]),

    #"Empty to Null" = Table.ReplaceValue(Source, "", null, (x,y,z) => if Text.Trim(x) = "" then null else Replacer.ReplaceValue(x,y,z), Table.ColumnNames(Source)),
    #"Combine by two" = TableCombineMultipleColumns(#"Empty to Null", 1),
    #"Demote and Transpose" = Table.Transpose(Table.DemoteHeaders(#"Combine by two")),
    #"Count Instances" = Table.CombineColumns(#"Demote and Transpose", List.RemoveFirstN(Table.ColumnNames(#"Demote and Transpose"), 1), List.NonNullCount, "Count"),
    #"Rename Column1" = Table.RenameColumns(#"Count Instances", {{"Column1", "Column Names"}})
in
    #"Rename Column1"

[Smauro]

Hi @G_Whit-UK ,

It's not clear what you're wishing to show, seeing that you have two "Field x" (yours and the counterpart) whereas in your result you display just one count, but you could start with this, which counts all the instances per column:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYicgRkexOtFKRnCuoZExmDTCUGMMNcMVzQADc31DA30jAyMDEMcMzgHpMcFiH8JEUzjX1MgQTBpjqDGDsMOxGRELAA==", 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]),

    #"Empty to Null" = Table.ReplaceValue(Source, "", null, (x,y,z) => if Text.Trim(x) = "" then null else Replacer.ReplaceValue(x,y,z), Table.ColumnNames(Source)),
    #"Demote and Transpose" = Table.Transpose(Table.DemoteHeaders(#"Empty to Null")),
    #"Count Instances" = Table.CombineColumns(#"Demote and Transpose", List.RemoveFirstN(Table.ColumnNames(#"Demote and Transpose"), 1), List.NonNullCount, "Count")
in
    #"Count Instances"

[AlB]

Hi @G_Whit-UK 

Paste the following M code in a blank query to see the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYicgRkexOtFKRnCuoZExmDTCUGMMNcMVzQADc31DA30jAyMDEMcMzgHpMcFiH8JEUzjX1MgQTBpjqDGDsMOxGRELAA==", 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]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Transaction ID", Int64.Type}, {"My Data Field 1", type text}, {"Counterpart Data Field 1", type text}, {"My Data Field 2", Int64.Type}, {"Counterpart Data Field 2", Int64.Type}, {"My Data Field 3", type text}, {"Counterpart Data Field 3", type text}, {"My Data Field 4", type date}, {"Counterpart Data Field 4", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"My Data Field 1", "My Data Field 2", "My Data Field 3", "My Data Field 4"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Transaction ID"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"Attribute"}, {{"Count", each List.Count(List.Select([Value], each _<>"")), Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Attribute", Order.Ascending}})
in
    #"Sorted Rows"

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[G_Whit-UK]

Hi @AlB.  Unfortunatly the proposed solution doesn't stick to the requirements.  The steps cannot delete the "My Data" fields.  Thanks anyway.

[Fowmy · Accepted solution]

@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

[G_Whit-UK]

Thanks @Fowmy.  This looks like a viable solution, although I cannot filter out the data fields as it could hide some data scenarios.  I'll see if I can get this to work on the actual dataset after tweaking your suggestion.