Count

[Anonymous]

Hi 

I have 2 tables that have 200 coulmns each. 

I need to count a specific text like "TRUE" on each column and shown under of each column. 

how can i do that ? 

[Greg_Deckler]

So you want to add this count of TRUE as a row to your table?

[Anonymous]

yes, exactly 

[Greg_Deckler]

OK, that's doable. The basic technique is that you are going to create a separate branch in your M code query and then use table appending to squash the two branches together. Let me throw together an example.

[Greg_Deckler]

OK, here is what I did PBIX attached.

Look! Look! @ImkeF !! I solved a slightly harder than trivial Power Query problem and I didn't even have to bug you!!!! 🙂

[Anonymous]

Hi Greg 

thansk for that, I couldn't understand what happened 

I have enclosed a sample excel file.

tabel 1

ID	Car	estate	Seller	Sell	Country
1	BMW	NSW	ALI	$        22,000	A
2	Volvo	VIC	HELLEN	$        25,000	B
3	HY	NSW	ALI	Null	C
4	Benz	NSW	ALI	$        55,000	Null
5	Null	Null	Null	$        65,000	F
6	BMW	SA	FRED	Null	J
7	KIA	VIC	HELLEN	$        75,000	Null
8	HY	Null	Null	$        85,000	K
9	Null	NSW	ALI	Null	Z

tabel2 

ID	Country	City	Street	House	Unit
1	A	X	22	11	606
2	B	Y	36	33	671
3	C	A	Null	Null	Null
4	Null	O	88	77	588
5	F	Null	64	99	631
6	J	Null	Null	EA	Null
7	Null	R	55	TT	Null
8	K	C	87	335	789
9	Z	Q	22	Null	809

could you please show how many NULL we have on each column?

thanks for that 

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

I have created a sample for your reference, please check the following steps as below.

1. Dupilcate your table and transform it like that. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZVAxDsIwEPsKurlD09DcZSwIBpBAoA5A1B907f+xQ1AqGHyxzr5zkpTESSMD8AC6DsWxE9ogU5OEjR3wBHxg8VTVZZV8X+Yvyzz/HLRsa+8KmKGoovSgNPTgx2oKHIiRzH9CmHr6CzgM6xStwp27uXQc1w7mnjflvqb5JXSpxawz8QXcvt9Q1lkLfXoD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, #"Country " = _t, #"City " = _t, #"Street " = _t, #"House " = _t, #"Unit " = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Country ", type text}, {"City ", type text}, {"Street ", type text}, {"House ", type text}, {"Unit ", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] = "Null")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Attribute"}, {{"Count", each Table.RowCount(_), type number}}),
    #"Transposed Table" = Table.Transpose(#"Grouped Rows"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Street ", Int64.Type}, {"House ", Int64.Type}, {"Unit ", Int64.Type}, {"Country ", Int64.Type}, {"City ", Int64.Type}})
in
    #"Changed Type1"

  

2. Append tables to get the excepted result.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZVAxDsIwEPsKurlD09DcZSwIBpBAoA5A1B907f+xQ1AqGHyxzr5zkpTESSMD8AC6DsWxE9ogU5OEjR3wBHxg8VTVZZV8X+Yvyzz/HLRsa+8KmKGoovSgNPTgx2oKHIiRzH9CmHr6CzgM6xStwp27uXQc1w7mnjflvqb5JXSpxawz8QXcvt9Q1lkLfXoD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, #"Country " = _t, #"City " = _t, #"Street " = _t, #"House " = _t, #"Unit " = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Country ", type text}, {"City ", type text}, {"Street ", type text}, {"House ", type text}, {"Unit ", type text}}),
    #"Appended Query" = Table.Combine({#"Changed Type", #"Table (3)"})
in
    #"Appended Query"

[ImkeF]

Well done mate @Greg_Deckler  🙂

[Greg_Deckler]

I know it's dumb but I was so pleased with myself! 🙂