The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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 ?
Solved! Go to 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"
So you want to add this count of TRUE as a row to your table?
yes, exactly
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.
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!!!! 🙂
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
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"
Well done mate @Greg_Deckler 🙂
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
I know it's dumb but I was so pleased with myself! 🙂
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the July 2024 Power BI update to learn about new features.