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,
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êsCovering 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.
User | Count |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |