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
