Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
Can anyone suggest me,How to run below excel power query in to Power Bi?
I have used advance editor in power bi but getting continuous error.
"Expression Error:The column 'indicator' of the table wasn't found.
Details:Indicator
let Source = Excel.Workbook(File.Contents("C:\Users\abc\Desktop\Table.xlsx"), null, true), Table_Sheet = Source{[Item="Table",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Table_Sheet, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Receipt No", type number}, {"Indicator", type text}, {"Count", Int64.Type}, {"Rate", Int64.Type}, {"Region ID", Int64.Type}}), key = Table.AddColumn(Source, "key", each Text.BeforeDelimiter(Text.From([Receipt No], "ru-RU"), "."), type text), transform = Table.TransformColumns(key, {"Indicator", each Record.FieldOrDefault([N = -1, Y = 1], _, _)}), group = Table.Group(transform, {"key"}, {"a", each List.Sum(List.Combine({[Indicator],[Count],[Rate],[#"Region ID"]})), type number}), list = Table.SelectRows(group, each [a] = 0)[key], filter = Table.SelectRows(key, each not List.Contains(list, [key])), final = Table.RemoveColumns(filter,{"key"}) in final
Solved! Go to Solution.
Hi @Anonymous,
Could you please check the [Indicator] column in your excel file, if there is no column called "Indicator", it may occur your problem, or you could upload your excel file to have a test if possible.
let Source = Excel.Workbook(File.Contents("C:\Users\abc\Desktop\Table.xlsx"), null, true), Table_Sheet = Source{[Item="Table",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Table_Sheet, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Receipt No", type number}, {"Indicator", type text}, {"Count", Int64.Type}, {"Rate", Int64.Type}, {"Region ID", Int64.Type}}), key = Table.AddColumn(Source, "key", each Text.BeforeDelimiter(Text.From([Receipt No], "ru-RU"), "."), type text), transform = Table.TransformColumns(key, {"Indicator", each Record.FieldOrDefault([N = -1, Y = 1], _, _)}), group = Table.Group(transform, {"key"}, {"a", each List.Sum(List.Combine({[Indicator],[Count],[Rate],[#"Region ID"]})), type number}), list = Table.SelectRows(group, each [a] = 0)[key], filter = Table.SelectRows(key, each not List.Contains(list, [key])), final = Table.RemoveColumns(filter,{"key"}) in final
Regards,
Daniel He
Hi @Anonymous,
Could you please check the [Indicator] column in your excel file, if there is no column called "Indicator", it may occur your problem, or you could upload your excel file to have a test if possible.
let Source = Excel.Workbook(File.Contents("C:\Users\abc\Desktop\Table.xlsx"), null, true), Table_Sheet = Source{[Item="Table",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Table_Sheet, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Receipt No", type number}, {"Indicator", type text}, {"Count", Int64.Type}, {"Rate", Int64.Type}, {"Region ID", Int64.Type}}), key = Table.AddColumn(Source, "key", each Text.BeforeDelimiter(Text.From([Receipt No], "ru-RU"), "."), type text), transform = Table.TransformColumns(key, {"Indicator", each Record.FieldOrDefault([N = -1, Y = 1], _, _)}), group = Table.Group(transform, {"key"}, {"a", each List.Sum(List.Combine({[Indicator],[Count],[Rate],[#"Region ID"]})), type number}), list = Table.SelectRows(group, each [a] = 0)[key], filter = Table.SelectRows(key, each not List.Contains(list, [key])), final = Table.RemoveColumns(filter,{"key"}) in final
Regards,
Daniel He
Thank you so much Daniel.
Hi @Anonymous,
This is refering to your column Indicator, on the query editor try going step by step and checking where it breaks, then check what happen between the two steps.
Without having the data appears that the column Indicator does not exist on your table, but again don't have aniway to confirm.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português