Forum Discussion

ITSNev's avatar
ITSNev
Frequent Visitor
4 years ago
Solved

Unable to Combine Data - Custom Function

Hi,   At first I was unable to combine data due to reference other queries to steps, so I split this one query in to 3 seperate queries, but now the Power BI Refresh GW is complaining Processing...
Data Protection
Need Help
  • ImkeF's avatar
    ImkeF
    4 years ago

    Hi ITSNev ,
    this can indeed be a pain.
    Try combining these queries into one like so:

    let
FnGetProjects_ = 
(offset as number) =>
let
    //start_date=eRSActualsStartOfMonth,
    //end_date=MaxProjectEndDate,
    records = Number.ToText(offset),
    header = [  #"Authorization"="Bearer XXXXXXXXXXXXXXXXX",
                #"Content-Type"= "application/json"],
    content = "", //"{""last_date:ex"": [null, ""2022-05-1""]""}",
    url = "https://app.YYYYYYYYYYYYYYYY.cloud/rest/v1/",
    response = Web.Contents(url, 
        [RelativePath = "projects/search?",
            Query=[
                offset = records,
                limit = "500"
                ],
        Content=Text.ToBinary(content),Headers=header]),
    out = Json.Document(response,1252)
in
    out,
    Source = List.Generate (
    () => [offset = 500, actuals = #"FnGetProjects"( 0 ) ] ,
    //each [actuals][total_count] <> 0,
    each not List.IsEmpty([actuals][data]),
    each [offset = [offset] + 500, actuals = #"FnGetProjects_" ([offset]) ],
    each [actuals]
),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"data"}, {"data"}),
    #"Expanded data" = Table.ExpandListColumn(#"Expanded Column1", "data"),
    #"Expanded data1" = Table.ExpandRecordColumn(#"Expanded data", "data", {"XXXXXX"})
in
    #"Expanded data1"
ImkeF's avatar
ImkeF
Community Champion
4 years ago

Hi ITSNev ,
this can indeed be a pain.
Try combining these queries into one like so:

let
FnGetProjects_ = 
(offset as number) =>
let
    //start_date=eRSActualsStartOfMonth,
    //end_date=MaxProjectEndDate,
    records = Number.ToText(offset),
    header = [  #"Authorization"="Bearer XXXXXXXXXXXXXXXXX",
                #"Content-Type"= "application/json"],
    content = "", //"{""last_date:ex"": [null, ""2022-05-1""]""}",
    url = "https://app.YYYYYYYYYYYYYYYY.cloud/rest/v1/",
    response = Web.Contents(url, 
        [RelativePath = "projects/search?",
            Query=[
                offset = records,
                limit = "500"
                ],
        Content=Text.ToBinary(content),Headers=header]),
    out = Json.Document(response,1252)
in
    out,
    Source = List.Generate (
    () => [offset = 500, actuals = #"FnGetProjects"( 0 ) ] ,
    //each [actuals][total_count] <> 0,
    each not List.IsEmpty([actuals][data]),
    each [offset = [offset] + 500, actuals = #"FnGetProjects_" ([offset]) ],
    each [actuals]
),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"data"}, {"data"}),
    #"Expanded data" = Table.ExpandListColumn(#"Expanded Column1", "data"),
    #"Expanded data1" = Table.ExpandRecordColumn(#"Expanded data", "data", {"XXXXXX"})
in
    #"Expanded data1"
  • Element115's avatar
    Element115
    Memorable Member
    3 years ago

    Regarding my last question about syntax, I found the answer.

     

    addColumn = (T as table, new_col_name as text, f as function, col_name as text, token as text, col_type as type) =>
    let
		new_col	= Table.AddColumn(
				T
				,   new_col_name				//"handleID"
				,   each f(Record.Field(_, col_name), token)		//getHandleID([ID_External], getToken())
				,   col_type
			)
	in
		new_col,

     

    Credit goes to Lz-3068 who suggested this answer here:
    https://learn.microsoft.com/en-us/answers/questions/253951/using-a-variable-as-a-column-name-in-power-query.html

  • Element115's avatar
    Element115
    Memorable Member
    3 years ago

    Imke, I think I found the solution. Will publish it here tomorrow after some testing to make sure.

  • Element115's avatar
    Element115
    Memorable Member
    3 years ago

    So I got a response from Microsoft.  Basically all this nonsense is because they are trying to prevent data leakage and side-channel attacks.  The issue is due to the data privacy firewall inside PQ as explained here:  https://learn.microsoft.com/en-us/power-query/dataprivacyfirewall .

    Imke, you are correct.  Being green behind the ears, I misunderstood the definition of 'query.'  I had defined a project_id in the query pane, but coming from a software engineering background, I automatically started thinking of 

     

     

    project_id = ######,

     

     

     as a variable with global scope and not a query.  Therefore when I brought all the queries underlined in green as shown in this screenshot:

    into the RT_table query as inline code, I forgot to also bring in project_id (red box).  And because it is a local query being referenced inside the RT_Data query where all the REST web API calls occur, the Service kept issuing the PrivacyError message.  I also had forgotten to substitute OLC:

     

     

    let
    Source = OLC,
...

     

     

    for the inline code referencing the SQL on-prem data source:

     

     

        Source                  = Sql.Databases("DB_instance"),
    db                      = Source{[Name="DB_name"]}[Data],
    dbo_some_view           = db{[Schema="dbo",Item="some_view"]}[Data],

     

     

    But once the project_id and the database referencing was inline, the Service stopped complaining and I could refesh at will all day long!  Ahhh life is good  😎