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.
Hello
I am trying to use power query to create a table that contains profiling data that is returned by Table.Profile() for all tables. The idea is to have one table that contains table/column reference for the rest of the tables in the model, essentially creating a fact table of dataset profiling data. The current poqer query steps I use show the data perfectly in the power query editor but I get this error when I apply to the power bi report: 'We cannot convert a value of type Function to type Table..'
Below I'll paste the power query scripts I am using, these should be applicable to any model as the aim is to create a generic set of steps that can be applied to all future models as a fast way to implement a complete data profiling table.
here are the steps I have:
// function called Table Profile that is used in the table steps
(tablename as table) =>
let
#"Converted to Table" = Table.Profile(#"tablename")
in
#"Converted to Table"
// table called Dataset Profile
let
Source = #sections,
Section1 = Source[Section1],
#"Converted to Table" = Record.ToTable(Section1),
#"Invoked Custom Function" = Table.AddColumn(#"Converted to Table", "Table Profile", each #"Table Profile"([Value])),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Invoked Custom Function", {"Value"}),
#"Removed Bottom Rows" = Table.RemoveLastN(#"Removed Errors",2), // this removes the function from this list and this table in my model, check yours to make sure they also appear as the last 2 rows.
#"Expanded Table Profile" = Table.ExpandTableColumn(#"Removed Bottom Rows", "Table Profile", {"Column", "Min", "Max", "Average", "StandardDeviation", "Count", "NullCount", "DistinctCount"}, {"Table Profile.Column", "Table Profile.Min", "Table Profile.Max", "Table Profile.Average", "Table Profile.StandardDeviation", "Table Profile.Count", "Table Profile.NullCount", "Table Profile.DistinctCount"})
in
#"Expanded Table Profile"
//
any help is much appreciated, it is extremely frustrating to have the steps work perfectly inside power query editor then fail on the load to power bi.
If there is a better method of getting what I want, please let me know. My first proper experience with power query has been this task so I am open to critisism of my methods and advice.
thanks
Hi @Anonymous ,
Sorry to disturb you...
But did I answer your question ? Please mark my reply as solution. Thank you very much.
Best Regards,
Stephen Tao
Hello,
Sorry for the late response. My focus has been taken elsewhere and i haven't been able to test your solution fully, but I am unsure what action I could take based off your reply.
the data source is the data quality of a dataset that itself loads without issue (it is the data quality tables i created in power query that are the problem). How can I give power bi desktop the correct permissions?
I may have mixed data types during a step in the power query. will the load to power bi fail if any power query step returns mixed data types, or only the final output?
thanks
Hi @Anonymous ,
Based on my research, it semms like you lack the relevant credentials of the data source in Power BI Desktop.
Also, this happens if you have mixed data types in your column to expand.
Similar post: Expression.Error: We cannot convert a value of type Record to type Function.
Cannot convert value to type table
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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.