Hello. I am trying to use an Azure storage blob as a data source for a dataflow, but I'm having problem everytime I try to save it. I first created a query on PowerBI Desktop, and the below query works. I can use the dataset, see the data and everything:
let
Source = Xml.Tables(Web.Contents("<MY SAS>" & "&restype=container&comp=list", [Headers=[Accept="application/json;odata=nometadata"]])),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Attribute:ServiceEndpoint", type text}, {"Attribute:ContainerName", type text}}),
Blobs = #"Changed Type"{0}[Blobs],
Blob = Blobs{0}[Blob],
#"Changed Type1" = Table.TransformColumnTypes(Blob,{{"Name", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "FileURL", each "<MY CONTAINER URL>" & [Name] & "<MY SAS QUERYSTRING>"),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"FileURL"}),
#"Added Custom1" = Table.AddColumn(#"Removed Columns", "NameTrimmed", each Text.Replace([Name], " ", "%20")),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "FileURL", each "<MY CONTAINER URL>" & [NameTrimmed] & "<MY SAS QUERYSTRING>")
, BinaryURLContents = Table.AddColumn(#"Added Custom", "BinaryFiles", each Table.PromoteHeaders(Csv.Document(Web.Contents([FileURL]), [Delimiter=",", Encoding=1250]), [PromoteAllScalars=true])),
#"Removed Other Columns" = Table.SelectColumns(BinaryURLContents,{"BinaryFiles"}),
#"Expanded BinaryFiles" = Table.ExpandTableColumn(#"Removed Other Columns", "BinaryFiles", {"AP/AR", "Year", "Period", "EntityCode", "EntityName", "EntityVATID", "Reporting Country", "Inv.Date", "PostingDate", "Doc.No.", "Invoice no.", "Reporting period", "Reporting currency", "Net amount (rep curr)", "VAT amount (rep curr)", "Gross amount (rep curr)", "Global currency", "Net amount (global curr)", "VAT amount (global curr)", "Gross amount (global curr)", "Tx.Code", "Tx.CodeDesc.", "VATRate", "VATCategory", "EYTaxCodeNET", "EYTaxCodeVAT", "Sales/Purchase", "Business partner number", "Business partner name", "Business partner VAT ID", "Business partner country", "Periodicity", "Due date", "Transaction type", "Business partner Address", "Business partner postal code", "Comments ", "GL account", "GL account description"}, {"BinaryFiles.AP/AR", "BinaryFiles.Year", "BinaryFiles.Period", "BinaryFiles.EntityCode", "BinaryFiles.EntityName", "BinaryFiles.EntityVATID", "BinaryFiles.Reporting Country", "BinaryFiles.Inv.Date", "BinaryFiles.PostingDate", "BinaryFiles.Doc.No.", "BinaryFiles.Invoice no.", "BinaryFiles.Reporting period", "BinaryFiles.Reporting currency", "BinaryFiles.Net amount (rep curr)", "BinaryFiles.VAT amount (rep curr)", "BinaryFiles.Gross amount (rep curr)", "BinaryFiles.Global currency", "BinaryFiles.Net amount (global curr)", "BinaryFiles.VAT amount (global curr)", "BinaryFiles.Gross amount (global curr)", "BinaryFiles.Tx.Code", "BinaryFiles.Tx.CodeDesc.", "BinaryFiles.VATRate", "BinaryFiles.VATCategory", "BinaryFiles.EYTaxCodeNET", "BinaryFiles.EYTaxCodeVAT", "BinaryFiles.Sales/Purchase", "BinaryFiles.Business partner number", "BinaryFiles.Business partner name", "BinaryFiles.Business partner VAT ID", "BinaryFiles.Business partner country", "BinaryFiles.Periodicity", "BinaryFiles.Due date", "BinaryFiles.Transaction type", "BinaryFiles.Business partner Address", "BinaryFiles.Business partner postal code", "BinaryFiles.Comments ", "BinaryFiles.GL account", "BinaryFiles.GL account description"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded BinaryFiles",{{"BinaryFiles.AP/AR", "AP/AR"}, {"BinaryFiles.Year", "Year"}, {"BinaryFiles.Period", "Period"}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"AP/AR", type text}, {"Year", Int64.Type}, {"Period", Int64.Type}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type2",{{"BinaryFiles.EntityCode", "EntityCode"}}),
#"Changed Type3" = Table.TransformColumnTypes(#"Renamed Columns1",{{"EntityCode", type text}}),
#"Renamed Columns2" = Table.RenameColumns(#"Changed Type3",{{"BinaryFiles.EntityName", "EntityName"}}),
#"Changed Type4" = Table.TransformColumnTypes(#"Renamed Columns2",{{"EntityName", type text}}),
#"Renamed Columns3" = Table.RenameColumns(#"Changed Type4",{{"BinaryFiles.EntityVATID", "EntityVATID"}}),
#"Changed Type5" = Table.TransformColumnTypes(#"Renamed Columns3",{{"EntityVATID", type text}}),
#"Renamed Columns4" = Table.RenameColumns(#"Changed Type5",{{"BinaryFiles.Reporting Country", "Reporting Country"}}),
#"Changed Type6" = Table.TransformColumnTypes(#"Renamed Columns4",{{"Reporting Country", type text}}),
#"Renamed Columns5" = Table.RenameColumns(#"Changed Type6",{{"BinaryFiles.Inv.Date", "Invoice Date"}, {"Reporting Country", "Reporting Country Code"}, {"BinaryFiles.PostingDate", "Posting Date"}}),
#"Changed Type7" = Table.TransformColumnTypes(#"Renamed Columns5",{{"Posting Date", type date}, {"Invoice Date", type date}}),
#"Renamed Columns6" = Table.RenameColumns(#"Changed Type7",{{"BinaryFiles.Doc.No.", "Doc No"}}),
#"Changed Type8" = Table.TransformColumnTypes(#"Renamed Columns6",{{"Doc No", type text}}),
#"Renamed Columns7" = Table.RenameColumns(#"Changed Type8",{{"BinaryFiles.Invoice no.", "Invoice No"}}),
#"Changed Type9" = Table.TransformColumnTypes(#"Renamed Columns7",{{"Invoice No", type text}}),
#"Renamed Columns8" = Table.RenameColumns(#"Changed Type9",{{"BinaryFiles.Reporting period", "Reporting Period"}}),
#"Changed Type10" = Table.TransformColumnTypes(#"Renamed Columns8",{{"Reporting Period", type date}}),
#"Renamed Columns9" = Table.RenameColumns(#"Changed Type10",{{"BinaryFiles.Reporting currency", "Reporting currency"}}),
#"Changed Type11" = Table.TransformColumnTypes(#"Renamed Columns9",{{"Reporting currency", type text}}),
#"Renamed Columns10" = Table.RenameColumns(#"Changed Type11",{{"Reporting currency", "Reporting Currency"}, {"BinaryFiles.Net amount (rep curr)", "Net Amount (Rep Curr)"}}),
#"Changed Type12" = Table.TransformColumnTypes(#"Renamed Columns10",{{"Net Amount (Rep Curr)", type number}}),
#"Renamed Columns11" = Table.RenameColumns(#"Changed Type12",{{"BinaryFiles.VAT amount (rep curr)", "VAT Amount (Rep Curr)"}}),
#"Changed Type13" = Table.TransformColumnTypes(#"Renamed Columns11",{{"VAT Amount (Rep Curr)", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type13"," - ","",Replacer.ReplaceValue,{"VAT Amount (Rep Curr)"}),
#"Changed Type14" = Table.TransformColumnTypes(#"Replaced Value",{{"VAT Amount (Rep Curr)", type number}}),
#"Renamed Columns12" = Table.RenameColumns(#"Changed Type14",{{"BinaryFiles.Gross amount (rep curr)", "Gross Amount (Rep Curr)"}, {"BinaryFiles.Global currency", "Global Currency"}}),
#"Changed Type15" = Table.TransformColumnTypes(#"Renamed Columns12",{{"Global Currency", type text}}),
#"Renamed Columns13" = Table.RenameColumns(#"Changed Type15",{{"BinaryFiles.Net amount (global curr)", "Net Amount (Global Curr)"}}),
#"Changed Type16" = Table.TransformColumnTypes(#"Renamed Columns13",{{"Net Amount (Global Curr)", type number}, {"Gross Amount (Rep Curr)", type number}}),
#"Renamed Columns14" = Table.RenameColumns(#"Changed Type16",{{"BinaryFiles.VAT amount (global curr)", "VAT Amount (Global Curr)"}}),
#"Replaced Value1" = Table.ReplaceValue(#"Renamed Columns14"," - ","",Replacer.ReplaceValue,{"VAT Amount (Global Curr)"}),
#"Changed Type17" = Table.TransformColumnTypes(#"Replaced Value1",{{"VAT Amount (Global Curr)", type number}}),
#"Renamed Columns15" = Table.RenameColumns(#"Changed Type17",{{"BinaryFiles.Gross amount (global curr)", "Gross Amount (Global Curr)"}}),
#"Changed Type18" = Table.TransformColumnTypes(#"Renamed Columns15",{{"Gross Amount (Global Curr)", type number}}),
#"Renamed Columns16" = Table.RenameColumns(#"Changed Type18",{{"BinaryFiles.Tx.Code", "Tax Code"}}),
#"Changed Type19" = Table.TransformColumnTypes(#"Renamed Columns16",{{"BinaryFiles.Tx.CodeDesc.", type text}, {"BinaryFiles.VATRate", type text}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type19",{"BinaryFiles.Tx.CodeDesc.", "BinaryFiles.VATRate", "BinaryFiles.VATCategory", "BinaryFiles.EYTaxCodeNET", "BinaryFiles.EYTaxCodeVAT", "BinaryFiles.Sales/Purchase"}),
#"Renamed Columns17" = Table.RenameColumns(#"Removed Columns1",{{"BinaryFiles.Business partner number", "Business Partner Number"}, {"BinaryFiles.Business partner name", "Business Partner Name"}, {"BinaryFiles.Business partner VAT ID", "Business Partner VAT ID"}}),
#"Changed Type20" = Table.TransformColumnTypes(#"Renamed Columns17",{{"Tax Code", type text}, {"Business Partner Number", type text}, {"Business Partner Name", type text}, {"Business Partner VAT ID", type text}}),
#"Renamed Columns18" = Table.RenameColumns(#"Changed Type20",{{"BinaryFiles.Business partner country", "Business Partner Country"}}),
#"Changed Type21" = Table.TransformColumnTypes(#"Renamed Columns18",{{"Business Partner Country", type text}}),
#"Renamed Columns19" = Table.RenameColumns(#"Changed Type21",{{"BinaryFiles.Periodicity", "Periodicity"}}),
#"Changed Type22" = Table.TransformColumnTypes(#"Renamed Columns19",{{"Periodicity", type text}}),
#"Renamed Columns20" = Table.RenameColumns(#"Changed Type22",{{"BinaryFiles.Due date", "Due date"}}),
#"Changed Type23" = Table.TransformColumnTypes(#"Renamed Columns20",{{"Due date", type text}}),
#"Renamed Columns21" = Table.RenameColumns(#"Changed Type23",{{"BinaryFiles.Transaction type", "Transaction Type"}, {"BinaryFiles.Business partner Address", "Business Partner Address"}, {"BinaryFiles.Business partner postal code", "Business Partner Postal Code"}, {"BinaryFiles.Comments ", "Comments"}, {"BinaryFiles.GL account", "GL Account"}}),
#"Changed Type24" = Table.TransformColumnTypes(#"Renamed Columns21",{{"Transaction Type", type text}, {"Business Partner Address", type text}, {"Business Partner Postal Code", type text}, {"Comments", type text}, {"GL Account", type text}}),
#"Renamed Columns22" = Table.RenameColumns(#"Changed Type24",{{"BinaryFiles.GL account description", "GL Account Description"}}),
#"Changed Type25" = Table.TransformColumnTypes(#"Renamed Columns22",{{"GL Account Description", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type25", "Due date", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Due date.1", "Due date.2", "Due date.3"}),
#"Changed Type26" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Due date.1", Int64.Type}, {"Due date.2", Int64.Type}, {"Due date.3", Int64.Type}}),
#"Added Custom3" = Table.AddColumn(#"Changed Type26", "Due Date", each Number.ToText([Due date.2]) & "/" & Number.ToText([Due date.1]) & "/" & Number.ToText([Due date.3])),
#"Changed Type27" = Table.TransformColumnTypes(#"Added Custom3",{{"Due Date", type date}}),
#"Reordered Columns" = Table.ReorderColumns(#"Changed Type27",{"AP/AR", "Year", "Period", "EntityCode", "EntityName", "EntityVATID", "Reporting Country Code", "Invoice Date", "Posting Date", "Doc No", "Invoice No", "Reporting Period", "Reporting Currency", "Net Amount (Rep Curr)", "VAT Amount (Rep Curr)", "Gross Amount (Rep Curr)", "Global Currency", "Net Amount (Global Curr)", "VAT Amount (Global Curr)", "Gross Amount (Global Curr)", "Tax Code", "Business Partner Number", "Business Partner Name", "Business Partner VAT ID", "Business Partner Country", "Periodicity", "Due date.1", "Due date.2", "Due date.3", "Due Date", "Transaction Type", "Business Partner Address", "Business Partner Postal Code", "Comments", "GL Account", "GL Account Description"}),
#"Removed Columns2" = Table.RemoveColumns(#"Reordered Columns",{"Due date.1", "Due date.2", "Due date.3"})
in
#"Removed Columns2"The problem is when I use this query as a data source for sa dataflow. If I use this query, I can still see a preview of the data, but when I try to save it, it will always give me this error:
Can't save dataflow
Your dataflow can't be saved because one or more entities references a dynamic data source.
I did some debugging that this line is giving the error:
BinaryURLContents = Table.AddColumn(#"Added Custom", "BinaryFiles", each Table.PromoteHeaders(Csv.Document(Web.Contents([FileURL]), [Delimiter=",", Encoding=1250]), [PromoteAllScalars=true]))
But this line is the one I use to read the contents as CSV. I don't know anything that I can use to modify it.
Any help will be appreciated.
Also note that:
1. I can't use an Azure Access Key for security reasons
2. I can only use SAS (Shared Access Signature)
