Excel Power Query into Power Bi

[Anonymous]

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

[v-danhe-msft · Accepted 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

[Anonymous]

Thank you so much Daniel.

[MFelix]

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