cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Gerdanel1
Frequent Visitor

column in table missing

Hi 

Feeling very frustated at this moment. I am faced with a problem I cant seem to solve. 

My report is indicating that a column "bill" in a table is missing and that causes my invoke function to fail.

Error as follows:

 

"An error occurred in the ‘Get PNP Prov Billing - Cabbon’ query. Expression.Error: The column 'bill' of the table wasn't found.
Details:
bill"

 

I have carefully traced my steps in and can confirm that the the column was not deleted. 

 

This boggles my mind as the report worked perfectly previously and now it doesnt and I havent changed anything.

Can anybody please help me??

 

let
    Source = (AccountID as text,StartDate as text,EndDate as text)=>

let
    Source = Xml.Tables(Web.Contents("http://rms-cabbon.pnpscada.com:8080/getProvisionalBill.jsp?LOGIN=rms.gerda@remotemetering.net&PWD=gerda&key1="&AccountID&"&startdate="&StartDate&"&enddate="&EndDate&"&")),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"result", type text}}),
    #"Expanded bill" = Table.ExpandTableColumn(#"Changed Type", "bill", {"li"}, {"bill.li"}),
    #"Expanded bill.li" = Table.ExpandTableColumn(#"Expanded bill", "bill.li", {"tname", "desc1", "desc2", "units", "rate", "amount", "unitsunit", "type", "compareId", "tarifflineid", "taxed"}, {"bill.li.tname", "bill.li.desc1", "bill.li.desc2", "bill.li.units", "bill.li.rate", "bill.li.amount", "bill.li.unitsunit", "bill.li.type", "bill.li.compareId", "bill.li.tarifflineid", "bill.li.taxed"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded bill.li",".",",",Replacer.ReplaceText,{"bill.li.amount"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"bill.li.amount", Int64.Type}}),
    #"Replaced Value1" = Table.ReplaceValue(#"Changed Type1",".",",",Replacer.ReplaceText,{"bill.li.units"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Replaced Value1",{{"bill.li.units", Currency.Type}}),
    #"Expanded readings" = Table.ExpandTableColumn(#"Changed Type2", "readings", {"mr"}, {"readings.mr"}),
    #"Expanded readings.mr" = Table.ExpandTableColumn(#"Expanded readings", "readings.mr", {"snumber"}, {"readings.mr.snumber"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded readings.mr",{"result"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "startdate", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"startdate.1", "startdate.2"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"startdate.1", type date}, {"startdate.2", type time}, {"enddate", type datetime}, {"published", type text}, {"key1", Int64.Type}, {"key2", type text}, {"util", type text}, {"currency", type text}, {"align", type text}, {"tariff", type text}, {"taxtype", type text}, {"taxperc", Int64.Type}, {"currency_decimals", Int64.Type}, {"documentdate", type datetime}, {"items", Int64.Type}, {"total_nontax", Int64.Type}, {"total_taxed", type text}, {"tax", type text}, {"total", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type3", {{"enddate", type text}}, "en-ZA"), "enddate", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"enddate.1", "enddate.2"}),
    #"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"enddate.1", type date}, {"enddate.2", type time}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type4",{"startdate.2", "enddate.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"startdate.1", "StartDate"}, {"enddate.1", "EndDate"}}),
    #"Removed Columns2" = Table.RemoveColumns(#"Renamed Columns",{"published"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns2",{{"key1", "MeterAccount"}, {"key2", "AccountName"}, {"util", "Utility"}}),
    #"Removed Columns3" = Table.RemoveColumns(#"Renamed Columns1",{"currency", "align"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns3",{{"tariff", "Tariff"}}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Renamed Columns2", "Tariff", Splitter.SplitTextByEachDelimiter({";"}, QuoteStyle.Csv, false), {"Tariff.1", "Tariff.2"}),
    #"Split Column by Delimiter3" = Table.SplitColumn(#"Split Column by Delimiter2", "Tariff.1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Tariff.1.1", "Tariff.1.2"}),
    #"Changed Type5" = Table.TransformColumnTypes(#"Split Column by Delimiter3",{{"Tariff.1.1", type text}, {"Tariff.1.2", type text}, {"Tariff.2", type text}}),
    #"Replaced Value2" = Table.ReplaceValue(#"Changed Type5",":","",Replacer.ReplaceText,{"Tariff.1.1"}),
    #"Renamed Columns3" = Table.RenameColumns(#"Replaced Value2",{{"Tariff.1.1", "Supplier"}, {"Tariff.1.2", "TariffSet"}, {"Tariff.2", "TariffCode"}}),
    #"Removed Columns4" = Table.RemoveColumns(#"Renamed Columns3",{"taxtype"}),
    #"Renamed Columns4" = Table.RenameColumns(#"Removed Columns4",{{"taxperc", "TaxPerc"}}),
    #"Removed Columns5" = Table.RemoveColumns(#"Renamed Columns4",{"currency_decimals", "items"}),
    #"Renamed Columns5" = Table.RenameColumns(#"Removed Columns5",{{"bill.li.tname", "Charge Description"}}),
    #"Removed Columns6" = Table.RemoveColumns(#"Renamed Columns5",{"bill.li.desc1", "bill.li.desc2"}),
    #"Renamed Columns6" = Table.RenameColumns(#"Removed Columns6",{{"bill.li.rate", "Rate"}}),
    #"Replaced Value3" = Table.ReplaceValue(#"Renamed Columns6",".",",",Replacer.ReplaceText,{"Rate"}),
    #"Changed Type6" = Table.TransformColumnTypes(#"Replaced Value3",{{"Rate", Currency.Type}, {"bill.li.amount", type number}}),
    #"Renamed Columns7" = Table.RenameColumns(#"Changed Type6",{{"bill.li.unitsunit", "ReadingType"}}),
    #"Removed Columns7" = Table.RemoveColumns(#"Renamed Columns7",{ "bill.li.taxed", "total_nontax"}),
    #"Replaced Value4" = Table.ReplaceValue(#"Removed Columns7",".",",",Replacer.ReplaceText,{"total_taxed"}),
    #"Changed Type7" = Table.TransformColumnTypes(#"Replaced Value4",{{"total_taxed", Currency.Type}}),
    #"Removed Columns8" = Table.RemoveColumns(#"Changed Type7",{"total_taxed", "tax", "total", "bill.li.tarifflineid"}),
    #"Renamed Columns8" = Table.RenameColumns(#"Removed Columns8",{{"readings.mr.snumber", "MeterNumber"}})
in
    #"Renamed Columns8"
in
    Source

 

 

 

1 REPLY 1
v-cherch-msft
Microsoft
Microsoft

Hi @Gerdanel1

 

It seems you may deal with the missing column.Please check if this article could help you.

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors