M power query - rebuild this data combination

Question

Getting the error message on the below query in M, I've tried splitting into 2 different queries before the merge step but still getting the 'this does not directly access a data source, please rebuild the data combination' error

*let
    Source = #"Query List from SP",
    cols = if {"Type"} = "Indicator" then 4 else 5,
    DataLoad = (path) =>
    let
       Doc =
           Csv.Document(
               Web.Contents(
                    BaseUrl,
                    [Headers =[#"Content-type"="text/csv",
                               #"Authorization"="Basic " & Credential],
                     RelativePath=path
                    ]), [Delimiter=",", Columns=cols, Encoding=65001, QuoteStyle=QuoteStyle.None]),
                    #"Promoted Headers" = Table.PromoteHeaders(Doc, [PromoteAllScalars=true]),
                    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Data", type text}, {"Organisation unit", type text}, {"Period", type text}, {"Value", type number}}
)
      in
         #"Changed Type",
     out = Table.AddColumn(
           Source,
           "res",
           each DataLoad([Path])
           ),
     #"Removed Errors" = Table.RemoveRowsWithErrors(out, {"res"}),
    #"Expanded res" = Table.ExpandTableColumn(#"Removed Errors", "res", {"Data", "Category option combo", "Organisation unit", "Period", "Value"}, {"Data", "Category option combo", "Organisation unit", "Period.1", "Value"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded res",{"GUIDs", "Period", "BaseUrl", "Credential", "Path"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Value", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Period.1", "Period"}}),
    #"Replace Cat" = Table.RenameColumns(Table.RemoveColumns(Table.AddColumn(#"Renamed Columns", "colnew", each if [Data] = "VbgQkVibB4P" then "qCWoTDpiKeH" 
else if [Data] = "YfCLQ1zig8z" then "qCWoTDpiKeH" 
else if [Data] = "M6kVOHWcnK3" then "qCWoTDpiKeH" 
else if [Data] = "Wov7FpjPr4E" then "qCWoTDpiKeH" 
else if [Data] = "MTuFYrvAacn" then "qCWoTDpiKeH" 
else if [Data] = "uXuoTc5nxkd" then "qCWoTDpiKeH" 
else if [Data] = "DsLkV0VYy6q" then "qCWoTDpiKeH"
else [Category option combo]),{"Category option combo"}),{{"colnew", "Category option combo"}}),
    #"Inserted Merged Column" = Table.AddColumn(#"Replace Cat", "deco key", each Text.Combine({[Data], [Category option combo]}, "."), type text),
    #"Added Index" = Table.AddIndexColumn(#"Inserted Merged Column", "Index", 1, 1)
in
    #"Added Index"*

Alexis Olson Alexis Olson · Accepted Answer · 2018-08-13T14:54:19

You are not directly accessing the data source since you are linking to the query #"Query List from SP".

Much has been written on this topic. Chris Webb has a whole blog series related to data privacy settings, but the easiest way to get rid of this problem is to simply ignore privacy levels.

Go to Options and settings under the file menu:

Under the Privacy section choose to ignore the privacy levels:

Obviously, if you're building something that needs to manage privacy levels carefully, then this is the wrong approach and you'll need to be more careful. But when privacy levels aren't a concern, this is a simple and convenient fix

M power query - rebuild this data combination

1 Answers