Results 1 to 5 of 5

PQ Merge table- cant solve the error

This is a discussion on PQ Merge table- cant solve the error within the Power BI forums, part of the Question Forums category; Hi I am trying to merge table (two coloumn from source table to two column in lookup table), below error ...

  1. #1
    Board Regular
    Join Date
    Oct 2004
    Location
    Dammam, KSA
    Posts
    206

    Default PQ Merge table- cant solve the error


    Hi
    I am trying to merge table (two coloumn from source table to two column in lookup table), below error is getting

    Formula.Firewall: Query 'SALESDATA' (step 'Merged Queries') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

    Source Table having
    GL Month
    D01 Jan
    D01 Jan
    D02 Feb
    D02 Jan
    (contain duplicate records)

    Lookup table Has

    GL Month Type
    D01 Jan S
    D01 Feb S
    D02 Jan S
    D02 Feb N
    D03 Jan NC
    so on...

    where i may be going wrong...
    sadyboy

  2. #2
    Board Regular
    Join Date
    Jan 2012
    Posts
    750

    Default Re: PQ Merge table- cant solve the error

    Seen that a few times. Can you post the M code?

  3. #3
    Board Regular MarcelBeug's Avatar
    Join Date
    Apr 2014
    Posts
    1,726

    Default Re: PQ Merge table- cant solve the error

    The Formula.Firewall error is typical for a situation in which you use results from one query directly in another.
    In other to prevent this error, you can first create your original queries separately (maybe with connection only) and then create a separate query in which you merge the date from the original queries.

    So not: query B in which you merge data with results from query A.
    But: query A, query B and then query C in which you merge A and B.
    Specializing in Power Query (M).
    Using Excel 2007 (Dutch) and Excel 2016 (Office 365 ProPlus) (Dutch/English), both on Windows 10.
    Array formulas can be recognised by the {} Excel puts around it automatically when you press Ctrl+Shift+Enter upon formula entry.

  4. #4
    Board Regular
    Join Date
    Oct 2004
    Location
    Dammam, KSA
    Posts
    206

    Default Re: PQ Merge table- cant solve the error

    let
    Source = Folder.Files("G:\OPERATIONS\DSR\DSRDATA\SALES"),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Extension", "Date accessed", "Date modified", "Date created", "Attributes"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each GETSALE([Folder Path]&[Name])),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Content", "Name", "Folder Path"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns1", "Custom", {"DATE", "GL", "SALES"}, {"DATE", "GL", "SALES"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each true),
    #"Added Custom1" = Table.AddColumn(#"Filtered Rows", "Date", each Date.ToText([DATE], "MMM")),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom1",{{"Date", "Month"}}),
    #"Added Custom2" = Table.AddColumn(#"Renamed Columns", "Custom", each Text.Upper([Month])),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Custom2",{"Month"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns2",{{"Custom", "Month"}}),
    #"Merged Queries" = Table.NestedJoin(#"Renamed Columns1",{"GL", "Month"},SAMESTORE,{"GL", "Month"},"NewColumn",JoinKind.LeftOuter)
    in
    #"Merged Queries"

    here is the code, till #"Renamed Columns1" it works. Getting error i Merge Queries.
    sadyboy

  5. #5
    Board Regular
    Join Date
    Jan 2012
    Posts
    750

    Default Re: PQ Merge table- cant solve the error

    Feared it might be a file. Can you post that somewhere?

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com