Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
My data is flowing from azure blob to PowerBI.
I am applying a series of data prep/cleaning in databricks. The result is a dataframe with 23 columns.
The column called "IsCracked" definitely exists in the csv in blob. It is a boolean T/F value.
PowerBI is connecting to azure blob; access key in place.
The advanced editor script is below. And the error points me to the "Changed Type" line:
#"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"", Int64.Type}, {"Category", type text}, {"Crack_Location", type text}, {"Date_Captured", type datetime}, {"Repair_Type", type text},
{"Unit", Int64.Type}, {"mm_1", Int64.Type}, {"mm_10", Int64.Type}, {"mm_2", Int64.Type}, {"mm_3", Int64.Type}, {"mm_4", Int64.Type}, {"mm_5", Int64.Type}, {"mm_6", Int64.Type}, {"mm_7", Int64.Type},
{"mm_8", Int64.Type}, {"mm_9", Int64.Type}, {"total_length", Int64.Type}, {"Largest_Crack", Int64.Type}, {"Largest_Crack_%_of_Total", Int64.Type}, {"Unit_Location", type text}, {"IsRepaired", type logical},
{"Crack_Count", Int64.Type}, {"IsCracked", type logical}}),
#"Extracted Text After Delimiter" = Table.TransformColumns(#"Changed Type", {{"Unit_Location", each Text.AfterDelimiter(_, "U_"), type text}}),
#"Added Conditional Column" = Table.AddColumn(#"Extracted Text After Delimiter", "IsCrackedLogicasNumber", each if [total_length] > 1 then 1 else 0),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Conditional Column",{{"IsCrackedLogicasNumber", Int64.Type}}),
#"Added Conditional Column1" = Table.AddColumn(#"Changed Type1", "IsRepairedasNumber", each if [IsRepaired] = true then 1 else 0),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Conditional Column1",{{"IsRepairedasNumber", Int64.Type}})
in
#"Changed Type2"
Tried refreshing, resetting access keys, double checking my code in databricks, the cols exist in the azure blob.
Grateful for any suggestions.
Solved! Go to Solution.
If your data source has variable column count then I recommend you remove the columns=xxx part from the M code.
Thanks for the pointer Ibendlin. I made some progress - I needed to update the number of columns and the column names in the Advanced Editor. I had added a column to the table I was creating in Databricks/Azure and sending it to PowerBI. PowerBI did not pick that up and I needed to update the M script. I will have a look at try and ? re conditionally working with cols as you suggest.
If your data source has variable column count then I recommend you remove the columns=xxx part from the M code.
Thanks for the tip.
While you figure out the discrepancy, learn about conditionally working with columns that may or may not be there , or that may or may not have a value. Check "try...otherwise" and the "?" operator.