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
