Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I use table.profile to get the data profile of a large dataset with many columns. I believe NullCount does not count Blank cells. How can i add CountBlank to this profile? Thanks.
Solved! Go to Solution.
Try this @fess440
I don't think you can have blanks for numerical fields - they get converted to nulls, and you cannot replace values with blanks, so I am assuming this is text.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIEYhOlWJ1opSQUXjKUB+akgBhQNoRpCGanQRUBebEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Data 1" = _t, #"Data 2" = _t, #"Data 3" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Data 2", type number}, {"Data 3", type number}}),
Custom1 =
Table.Profile(
#"Changed Type",
{
{
"Blanks", each Type.Is(_, type nullable any), each List.Count(List.Select(_, each _ = ""))
}
}
)
in
Custom1
This is my dummy data:
And this is the result. Note: I removed a bunch of columns Table.Profile generates to get this screenshot:
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingGreat @fess440 - glad I was able to help. I learned something new here too!
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingTry this @fess440
I don't think you can have blanks for numerical fields - they get converted to nulls, and you cannot replace values with blanks, so I am assuming this is text.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIEYhOlWJ1opSQUXjKUB+akgBhQNoRpCGanQRUBebEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Data 1" = _t, #"Data 2" = _t, #"Data 3" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Data 2", type number}, {"Data 3", type number}}),
Custom1 =
Table.Profile(
#"Changed Type",
{
{
"Blanks", each Type.Is(_, type nullable any), each List.Count(List.Select(_, each _ = ""))
}
}
)
in
Custom1
This is my dummy data:
And this is the result. Note: I removed a bunch of columns Table.Profile generates to get this screenshot:
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi Edhans
thanks for the solution but I don't understand the part :
Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIEYhOlWJ1opSQUXjKUB+akgBhQNoRpCGanQRUBebEA", BinaryEncoding.Base64), Compression.Deflate)),
my table comes is imported from a data base, not a json file, so how can I adapt this please,
cordially
walid
See specifically step 5 below:
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reportingor if I put the right column name instead of "data 2" for instance "cd_marque" between quotes as you did, and changed the type to text instead of number I get
Expression.Error: The column 'CD_MARQUE' of the table wasn't found.
Details:
CD_MARQUE
but it is wrong this column exists
just use this:
let
Source = Sql.Database("SESKRUTDEVDB05", "REF_PDT"),
dbo_ODS_PDT_MARQUE = Source{[Schema="dbo",Item="ODS_PDT_MARQUE"]}[Data],
Custom1 =
Table.Profile(
dbo_ODS_PDT_MARQUE,
{
{
"Blanks", each Type.Is(_, type nullable any), each List.Count(List.Select(_, each _ = ""))
}
}
)
in
Custom1
You don't need a Changed Type step because your data is coming from SQL Server and already has good data types.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reportingthank you very much
thank you very much for your answer, i think i am not far, because syntaxe is good but why should i transform the datatype of the columns, I want to keep them as it? and also should I replace the data 2 and 3 with real names of the columns because there are plenty and I will do it on many tables, so it may be very long?
let
Source = Sql.Database("SESKRUTDEVDB05", "REF_PDT"),
dbo_ODS_PDT_MARQUE = Source{[Schema="dbo",Item="ODS_PDT_MARQUE"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Data 2", type number}, {"Data 3", type number}}),
Custom1 =
Table.Profile(
#"Changed Type",
{
{
"Blanks", each Type.Is(_, type nullable any), each List.Count(List.Select(_, each _ = ""))
}
}
)
in
Custom1
@fess440 - did you try this solution?
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reportingand also if i ignore the changed type rows (both) i get an error message
Expression.Error: We cannot convert a value of type List to type Table.
Details:
Value=[List]
Type=[Type]
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.