Forum Discussion
Solved
Data Summary - table with mutliple columns
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 ...
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 🙂
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"
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