I have published a dataset that connects to a Power BI Semantic model and 2 folders on Sharepoint.
When I download the file and refresh it in the desktop it is successful however when it is refreshed in the online service it fails.
This is my error message
| Data source error: | Expression.Error: We cannot convert the value "[Record]" to type List.. Microsoft.Data.Mashup.ErrorCode = 10277. Value = [Record]. . The exception was raised by the IDbCommand interface. Table: External Delivery Invoices. |
| Cluster URI: | WABI-WEST-EUROPE-B-PRIMARY-redirect.analysis.windows.net |
This is the Power Query code for the table that is giving the error (I have removed the URL of the source and some remove column/ change type values to make the code shorter to read.)
Please can someone help me understand where the error is being produced.
I have read other posts that talk about privacy settings between desktop and online. I have ensured both are the same (Private) and I still recieve this error
let
Source = SharePoint.Files("--------", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".csv" or [Extension] = ".xlsx") and ([Folder Path] = "-----------")),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each Text.StartsWith([Name], "France")),
#"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows1", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Filtered Hidden Files2" = Table.SelectRows(#"Renamed Columns1", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function2" = Table.AddColumn(#"Filtered Hidden Files2", "Transform File (2)", each #"Transform File (2)"([Content])),
#"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function2", {"Transform File (2)"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (2)", Table.ColumnNames(#"Transform File (2)"(#"Sample File (2)"))),
#"Filtered Rows2" = Table.SelectRows(#"Expanded Table Column1", each true),
#"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows2",{{"Column1", type text}, {"Column2", type any}, {"Détail - Facturation client", type text}}),
#"Filtered Rows4" = Table.SelectRows(#"Changed Type", each [Column1] <> null and [Column1] <> ""),
#"Sorted Rows" = Table.Sort(#"Filtered Rows4",{{"Column1", Order.Descending}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Sorted Rows", [PromoteAllScalars=true]),
#"Filtered Rows5" = Table.SelectRows(#"Promoted Headers", each ([#"N° pièce"] <> "Données de l'envoi" and [#"N° pièce"] <> "N° pièce")),
#"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows5",{{"N° pièce", Int64.Type}, {"Date pièce", type date},}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Mt HT /EXO Transport", "Base Transport Cost"}, {"Date pièce", "Vendor Invoice Date"},{"Mt HT/EXO Options et autres", "Surcharge - individual Options"}}),
#"Added Custom2" = Table.AddColumn(#"Renamed Columns", "Surcharge - Subtotal", each List.Sum({[#"Surcharge - individual Options"],[#"Surcharge - Fuel"],[Environmental Charge]})),
#"Replaced Value" = Table.ReplaceValue(#"Added Custom2",null,0,Replacer.ReplaceValue,{"Delivery B2C"}),
#"Added Custom" = Table.AddColumn(#"Replaced Value", "Surcharge - Other", each List.Sum({[#"Surcharge - individual Options"]})-List.Sum({[Delivery B2C]})),
#"Changed Type3" = Table.TransformColumnTypes(#"Added Custom",{{"Surcharge - Subtotal", type number}, {"Surcharge - Other", type number}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type3", "Source.Name", each "France Express"),
#"Added Custom3" = Table.AddColumn(#"Added Custom1", "Currency", each "EUR"),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"Traitement manuel (HT)", "Modification d'écriture (HT)","Code agence traitante"}),
#"Appended Query" = Table.Combine({#"Removed Columns", UPS, TNT, DX}),
#"Filtered Rows3" = Table.SelectRows(#"Appended Query", each true),
#"AddedPrefix"= Table.AddColumn(#"Filtered Rows3", "SAP Delivery Number", each if Text.StartsWith([inSAP Delivery Number], "8") then "00" & [inSAP Delivery Number] else [inSAP Delivery Number]),
#"Changed Type2" = Table.TransformColumnTypes(AddedPrefix,{{"Surcharge - Dimension", type number}, {"Surcharge - Subtotal", type number}, {"Surcharge - Other", type number},{"Consignment Number", type text}, {"Vendor Invoice Number", type text}, {"SAP Delivery Number", type text}, {"Vendor Invoice Date", type date}}),
#"Removed Columns2" = Table.RemoveColumns(#"Changed Type2",{"inSAP Delivery Number", "Vendor Invoice Amount", "21.000 % Tax"}),
#"Renamed Columns4" = Table.RenameColumns(#"Removed Columns2",{{"Source.Name", "Vendor"},{"Environmental Charge", "Surcharge - Environmental Charge"}, {"Delivery B2C", "Surcharge - Delivery B2C"}, {"Non-Stackable Pallets", "Surcharge - Non stackable pallets"}})
in
#"Renamed Columns4"
