OlEDB or ODBC error: The name "" doesnt exist in the current context

[Anonymous]

Hello All,

Here is a background of what I am trying to achieve.

I have a text file which has the M query steps for a dataset from which I fetch the M query steps. When I add simple M query steps like Renaming a column in the text file and refresh the dataset in PowerBI desktop, it works fine. But when I add complex steps like Merge/Join another dataset in the text file and refresh the dataset, the data refresh fails!! Is this because the underlying Data model gets changed when I am joining another dataset ??

I donot have any exclusive relationships defined in the Data model, i read some posts related to this but that didnt help.

Here is a screenshot of the error that I get when I refresh the dataset.

This is text file that contains my Q query steps.

Please suggest if there is a solution to this.

Thanks,

Ajay

[v-jiascu-msft]

Hi Ajay,

Please refer to this wonderful blog: loading-power-query-m-code-from-text-files/. 

It seems you only need to add the second parameter "#shared". Please refer to the snapshot below.

Please understand that this link is provided “AS IS” with no warranties or guarantees of content changes, and confers no rights.

Best Regards,

[Anonymous]

Hi Jiascu,

Thanks for your response.

I already have the second parameter incorporated in my code- screenshot below. And this works great if I have simple M query steps like "Renaming a column". I get error when I added another step in my text file to join another dataset and I tried to refresh the dataset.

Thanks,

Ajay

[v-jiascu-msft]

Hi Ajay,

I tested the Merge tables (join) successfully. Can you share your code in Text mode that we can copy and paste here? Please delete the connection parameters first (the sensitive parts).

Best Regards,

[Anonymous]

Hi Jiascu,

Here is my M-query steps below. Please see the last 2 steps where I am joining another dataset.

let
FnGetOnePage =
(url) as record =>
let
Source = Json.Document(Web.Contents(url, [Headers=[#"Authorization"="bearer ******Auth_String here******"]])),
toTable = Table.FromRecords({Source}),
#"Expanded data" = Table.ExpandListColumn(toTable, "data"),
#"Expanded data1" = Table.ExpandRecordColumn(#"Expanded data", "data", {"id","title"}, {"data.id", "data.title"}),
#"Expanded links" = Table.ExpandRecordColumn(#"Expanded data1", "links", {"self", "last", "next"}, {"link_self", "link_last", "link_next"}),
linknext= #"Expanded links"[link_next],
#"Merged Columns" = Table.CombineColumns(#"Expanded data1",{"data.id", "data.title"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
id = try #"Merged Columns"[Merged] otherwise null,
next = try List.First(linknext) otherwise null,
res = [Id=id, Next=next]
in res,

GeneratedList =
List.Generate(
()=>[i=0, res = FnGetOnePage("******Data source URL here******")],
each [i]<50 and [res][Id]<>null,
each [i=[i]+1, res = FnGetOnePage([res][Next])],
each [res]),
#"Converted_toTable" = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded_Record" = Table.ExpandRecordColumn(#"Converted_toTable", "Column1", {"Id", "Next"}, {"Column1.Id", "Column1.Next"}),
#"Expanded_List" = Table.ExpandListColumn(#"Expanded_Record", "Column1.Id"),
#"Split_Column_by_Delimiter" = Table.SplitColumn(#"Expanded_List", "Column1.Id", Splitter.SplitTextByEachDelimiter({","}, QuoteStyle.Csv, false), {"Column1.Id.1", "Column1.Id.2"}),
#"Changed_Data_Type" = Table.TransformColumnTypes(#"Split_Column_by_Delimiter",{{"Column1.Id.1", Int64.Type}, {"Column1.Id.2", type text}}),
#"Renamed_Columns" = Table.RenameColumns(#"Changed_Data_Type",{{"Column1.Id.1", "Survey_Id"}, {"Column1.Id.2", "Survey_Name_New"}, {"Column1.Next", "URLNext_New"}}),
#"Merged Queries" = Table.NestedJoin(#"Renamed_Columns",{"Survey_Id"},Survey_Details,{"Survey_Id"},"Survey_Details",JoinKind.LeftOuter),
#"Expanded Query1" = Table.ExpandTableColumn(#"Merged Queries", "Survey_Details", {"Survey_Name", "Column1.Next"}, {"Survey_Details.Survey_Name", "Survey_Details.Column1.Next"})
in
#"Expanded Query1"

[v-jiascu-msft]

Hi @Anonymous ,

I find the query "Survey_Details" isn't in the file. That makes sense as the error message says. I would suggest you put it in the file too. If you'd like to use this function, why not add all the related parts in the file too? Please give it a try.

Best Regards,

[Anonymous]

Hi Jiascu,

Survey_Details dataset is loaded separately so its M-query steps arent available in this text file.

Can I load two datasets from a common text file source - kindly share the sample code if you have it handy.

Thanks,

Ajay

[v-jiascu-msft]

Hi @Anonymous ,

Could you please mark the proper answers as solutions?

Best Regards,

[v-jiascu-msft]

Hi Ajay,

Yes, we can add the two sources in the text file directly. Please refer to the simple demo below.

let
    // source 1
    Source1 = Sql.Database("Server", "DB"),
    dbo_BI_Salario = Source1{[Schema="dbo",Item="BI_Salario"]}[Data],

    // source 2
    Source2 = Excel.Workbook(File.Contents("D:\DataSource\ds.xlsx"), null, true),
    BI_Venda = Source2{[Item="Table1",Kind="Table"]}[Data],

    #"Merged Queries" = Table.NestedJoin(dbo_BI_Salario,{"Cpf_Vendedor"},BI_Venda,{"Cpf_Vendedor"},"BI_Venda",JoinKind.LeftOuter),
    #"Expanded BI_Venda" = Table.ExpandTableColumn(#"Merged Queries", "BI_Venda", {"Id_item", "Id_Venda", "Cpf_Vendedor", "Mes", "Ano", "Id_Produto", "Valor_Venda", "Lucro_Venda", "Comissao_Venda"}, {"BI_Venda.Id_item", "BI_Venda.Id_Venda", "BI_Venda.Cpf_Vendedor", "BI_Venda.Mes", "BI_Venda.Ano", "BI_Venda.Id_Produto", "BI_Venda.Valor_Venda", "BI_Venda.Lucro_Venda", "BI_Venda.Comissao_Venda"})
in
    #"Expanded BI_Venda"

Best Regards,