Hello Experts.
I am using Expression.Evaluate to run a step in power query, So I have added #shared along with the function to evaluate the functions, Everthing is working fine but problem is I am unable to save the Query Editor or report because of below Error.
Failed to save modifications to the server. Error returned: 'Expression in partition 'Sorting Columns-94311d0d-b0e4-49cd-baf6-601b7ef667c9' in table 'Sorting Columns' references an entity that is not supported. One example of unsupported entity is the function '#shared'. '.
Code:
let
Source = Excel.Workbook(File.Contents("C:\Users\Desktop\Power BI\test1.xlsx"), null, true),
Sheet2_Sheet = Source{[Item="Sheet2",Kind="Sheet"]}[Data],
PromotedHeaders = Table.PromoteHeaders(Sheet2_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(PromotedHeaders,{{"Test1", type text}, {"Test2", Int64.Type}, {"Test3", Int64.Type}, {"Test4", Int64.Type}}),
#"Col Names_Sort" = Table.ColumnNames(#"Changed Type"),
#"Generate" = List.Generate(() => [i=-1,x= Table.FromList(#"Col Names_Sort",Splitter.SplitByNothing())],
each [i]<List.Count(#"Col Names_Sort"),
each [
// x={i},
i=[i]+1,
p=Expression.Evaluate(
"Table.RowCount(Table.Distinct(Table.FromList(#"&"""Sheet2 (2)"""&"["&#"Col Names_Sort"{i}&"],Splitter.SplitByNothing(), null, null, ExtraValues.Error)))"
,#shared),
q=#"Col Names_Sort"{i},
x = Text.Combine({Number.ToText(p),q},", ")
//y={i}
],
each [x]),
#"Remove_1stRow" = Table.SplitColumn(Table.RemoveFirstN(Table.FromList(#"Generate",Splitter.SplitByNothing()),1),"Column1",Splitter.SplitTextByDelimiter(", ")),
#"Sort_Col" = Table.Sort(#"Remove_1stRow",{"Column1.1",Order.Descending}),
#"Renamed Columns" = Table.RenameColumns(Sort_Col,{{"Column1.1", "DCount"}, {"Column1.2", "Column Name"}})
in
#"Renamed Columns"
Not Sure how to resolve.
Any help is Appretiated.
Thanks
Siva
