Solved: Re: Append 2 tables - expression.error: a cyclic r...

CR · ‎09-20-2018

Hi everybody,

I need help because I don't understand why I get the error message expression.error: a cyclic reference was encountered during evaluation when I try to add the content of the table 2 in the table 1 with the function Append Queries.

For information, I got exactly the same column headers because, when I use Append Queries as New (creating the table 3), it works fine.

But, in my case, I just need to include table 2 in table 1.

Here is the code I got in table 1 MASTER PDB TEMP (with the last line causing the error message)

let
    Source = Excel.Workbook(Web.Contents("https://.../powerbi-dcc/Shared%20Documents/PROJECTS/AGCC/PDB.xlsx"), null, true),
    Document_Sheet = Source{[Item="Document",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Document_Sheet, [PromoteAllScalars=true]),
    #"Removed Columns" = Table.RemoveColumns(#"Promoted Headers",{"Client Reference", "Department", "Tag", "TN Reference", "TN Purpose", "Document Status"}),
    #"TN Date - rename Actual date" = Table.RenameColumns(#"Removed Columns",{{"TN Date", "Actual Date"}}),
    #"OC - rename Origin" = Table.RenameColumns(#"TN Date - rename Actual date",{{"OC", "Origin"}}),

    #"Discipline - first letter capital" = Table.TransformColumns(#"OC - rename Origin",{{"Discipline", Text.Proper}}),
    #"Discipline - CAD" = Table.ReplaceValue(#"Discipline - first letter capital","Cad","CAD", Replacer.ReplaceText,{"Discipline"}),
    #"Discipline - HVAC" = Table.ReplaceValue(#"Discipline - CAD","Hvac","HVAC",Replacer.ReplaceText,{"Discipline"}),
    #"Discipline - HSED" = Table.ReplaceValue(#"Discipline - HVAC","Hsed","HSED",Replacer.ReplaceText,{"Discipline"}),

    #"DOC-REF  - adding" = Table.AddColumn(#"Discipline - HSED", "DOC-REF", each if [Rev Purpose] <> "IFR" and [Rev Purpose] <> "IFD" and [Rev Purpose] <> "IFQ" then null else if [Rev Purpose] = "IFR" and [Class] = "1" and [Rev] = "B" then [Document Reference] & "_IFR2" else if [Rev Purpose] = "IFD" and [Class] = "2" and [Rev] = "1" then [Document Reference] & "_IFD2" else [Document Reference] & "_" & [Rev Purpose]),

    #"PD inital - merge query 1" = Table.NestedJoin(#"DOC-REF  - adding",{"DOC-REF"},JANUS,{"DOC-REF"},"JANUS",JoinKind.LeftOuter),
    #"PD inital - merge query 2" = Table.ExpandTableColumn(#"PD inital - merge query 1", "JANUS", {"INITIAL PLAN DATE"}, {"JANUS.INITIAL PLAN DATE"}),
    #"PD inital - rename" = Table.RenameColumns(#"PD inital - merge query 2",{{"JANUS.INITIAL PLAN DATE", "Planned Date"}}),

    #"PD revised - merge query 1" = Table.NestedJoin(#"PD inital - rename",{"DOC-REF"},JANUS,{"DOC-REF"},"JANUS",JoinKind.LeftOuter),
    #"PD revised - merge query 2" = Table.ExpandTableColumn(#"PD revised - merge query 1", "JANUS", {"REVISED PLAN DATE"}, {"JANUS.REVISED PLAN DATE"}),
    #"PD revised - rename" = Table.RenameColumns(#"PD revised - merge query 2",{{"JANUS.REVISED PLAN DATE", "Planned Date - revised"}}),

    #"FD - merge query 1" = Table.NestedJoin(#"PD revised - rename",{"DOC-REF"},JANUS,{"DOC-REF"},"JANUS",JoinKind.LeftOuter),
    #"FD - merge query 2" = Table.ExpandTableColumn(#"FD - merge query 1", "JANUS", {"FORECAST DATE"}, {"JANUS.FORECAST DATE"}),
    #"FD - rename" = Table.RenameColumns(#"FD - merge query 2",{{"JANUS.FORECAST DATE", "Forecast Date"}}),
    #"DOC-REF - remove" = Table.RemoveColumns(#"FD - rename",{"DOC-REF"}),
    #"Date - unpivot" = Table.Unpivot(#"DOC-REF - remove", {"Actual Date", "Forecast Date", "Planned Date"}, "Attribute", "Value"),

    #"Status Date et Date - rename" = Table.RenameColumns(#"Date - unpivot",{{"Attribute", "Status Date"}, {"Value", "Date"}}),

    #"Doc Type - adding" = Table.AddColumn(#"Status Date et Date - rename", "Doc type", each if Text.Contains([Document Reference], "CN") then "CN" else if Text.Contains([Document Reference], "DI") then "DI" else if Text.Contains([Document Reference], "DW") then "DW" else if Text.Contains([Document Reference], "EL") then "EL" else if Text.Contains([Document Reference], "FOR") then "FOR" else if Text.Contains([Document Reference], "IRC") then "IRC" else if Text.Contains([Document Reference], "ITP") then "ITP" else if Text.Contains([Document Reference], "JSD") then "JSD" else if Text.Contains([Document Reference], "JSM") then "JSM" else if Text.Contains([Document Reference], "JSS") then "JSS" else if Text.Contains([Document Reference], "ML") then "ML" else if Text.Contains([Document Reference], "MR") then "MR" else if Text.Contains([Document Reference], "MS") then "MS" else if Text.Contains([Document Reference], "MTO") then "MTO" else if Text.Contains([Document Reference], "NM") then "NM" else if Text.Contains([Document Reference], "PDS") then "PDS" else if Text.Contains([Document Reference], "PID") then "PID" else if Text.Contains([Document Reference], "PFD") then "PFD" else if Text.Contains([Document Reference], "PLG") then "PLG" else if Text.Contains([Document Reference], "PP") then "PP" else if Text.Contains([Document Reference], "RT") then "RT" else if Text.Contains([Document Reference], "SOW") then "SOW" else if Text.Contains([Document Reference], "SP") then "SP" else if Text.Contains([Document Reference], "SR") then "SR" else if Text.Contains([Document Reference], "STD") then "STD" else if Text.Contains([Document Reference], "TBT") then "TBT" else if Text.Contains([Document Reference], "DQP") then "DQP" else if Text.Contains([Document Reference], "STC") then "STC" else if Text.Contains([Document Reference], "TDS") then "TDS" else if Text.Contains([Document Reference], "DD") then "DD" else if Text.Contains([Document Reference], "DR") then "DR" else if Text.Contains([Document Reference], "GEA") then "GEA" else if Text.Contains([Document Reference], "LIS") then "LS" else if Text.Contains([Document Reference], "PQ") then "PQ" else if Text.Contains([Document Reference], "WI") then "WI" else null),
    #"OC - Adding" = Table.AddColumn(#"Doc Type - adding", "OC", each if Text.Contains([Origin], "POC") then "TechnipFMC Paris" else if Text.Contains([Origin], "ZOC") then "TechnipFMC benelux" else "TechnipFMC Paris"),
    #"Module - adding" = Table.AddColumn(#"OC - Adding", "Module", each "ENGINEERING"),
    #"Origin et PD revised - remove" = Table.RemoveColumns(#"Module - adding",{"Origin", "Planned Date - revised"}),
    #"Append JANUS TEMP" = Table.Combine({#"Origin et PD revised - remove", #"JANUS TEMP"})
in
    #"Append JANUS TEMP"

Now for table 2

let
    Source = Excel.Workbook(Web.Contents("https://.../powerbi-dcc/Shared%20Documents/PROJECTS/AGCC/JANUS.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"DELIVERABLE REFERENCE", type text}, {"CAT", type text}, {"DLV REF. P1: CONTRACT CODE", type text}, {"DLV REF. P2: UNIT CODE", Int64.Type}, {"DLV REF. P3: DOC/DLV TYPE", type text}, {"DLV REF. P4: ENGINEERING CODE", Int64.Type}, {"DLV REF. P5: SERIAL NUMBER", Int64.Type}, {"CLIENT REFERENCE", type any}, {"WEIGHT", Int64.Type}, {"MANUFACTURING WEIGHT", type any}, {"TITLE", type text}, {"MILESTONE CHAIN", type text}, {"DBS", type text}, {"WBS", Int64.Type}, {"FREE CRITERIA 1", type any}, {"FREE CRITERIA 2", type any}, {"FREE CRITERIA 3", type date}, {"FREE CRITERIA 4", type any}, {"MSCODE", type text}, {"CUMULATIVE", Int64.Type}, {"MANUFACTURING CUMULATIVE", type any}, {"EMPTY COL", type any}, {"OBS", type text}, {"INITIAL PLAN DATE", type date}, {"REVISED PLAN DATE", type date}, {"FORECAST DATE", type date}, {"ACTUAL DATE", type date}, {"PLANNING SYSTEM IMPORT DATE", type any}, {"DMS/MMS IMPORT DATE", type any}, {"EMPTY COL_1", type any}, {"EMPTY COL_2", type any}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"CAT", "DLV REF. P1: CONTRACT CODE", "DLV REF. P2: UNIT CODE", "DLV REF. P3: DOC/DLV TYPE", "DLV REF. P4: ENGINEERING CODE", "DLV REF. P5: SERIAL NUMBER", "CLIENT REFERENCE", "WEIGHT", "MANUFACTURING WEIGHT", "MILESTONE CHAIN", "DBS", "WBS", "FREE CRITERIA 1", "FREE CRITERIA 2", "FREE CRITERIA 3", "FREE CRITERIA 4", "CUMULATIVE", "MANUFACTURING CUMULATIVE", "EMPTY COL", "OBS", "PLANNING SYSTEM IMPORT DATE", "DMS/MMS IMPORT DATE", "EMPTY COL_1", "EMPTY COL_2", "ACTUAL DATE"}),
    #"Rev - adding" = Table.AddColumn(#"Removed Columns", "Rev", each null),
    #"Discipline - adding" = Table.AddColumn(#"Rev - adding", "Discipline", each null),
    #"INTERNAL (yes or NO) - adding" = Table.AddColumn(#"Discipline - adding", "INTERNAL (yes or NO)", each null),
    #"Specific Transmittal Number - adding" = Table.AddColumn(#"INTERNAL (yes or NO) - adding", "Specific Transmittal Number", each null),
    #"Class - adding" = Table.AddColumn(#"Specific Transmittal Number - adding", "Class", each null),
    #"Actual Date - adding" = Table.AddColumn(#"Class - adding", "Actual Date", each null),
    #"Columns - rename" = Table.RenameColumns(#"Actual Date - adding",{{"DELIVERABLE REFERENCE", "Document Reference"}, {"TITLE", "Document Title"}, {"INITIAL PLAN DATE", "Planned Date"}, {"REVISED PLAN DATE", "Planned Date - revised"}, {"FORECAST DATE", "Forecast Date"}, {"MSCODE", "Rev Purpose"}}),
    #"Rev Purpose - cleanning" = Table.SelectRows(#"Columns - rename", each ([Rev Purpose] = "IFD" or [Rev Purpose] = "IFD1" or [Rev Purpose] = "IFD2" or [Rev Purpose] = "IFQ" or [Rev Purpose] = "IFR" or [Rev Purpose] = "IFR1" or [Rev Purpose] = "IFR2")),
    #"Rev Purpose - IFD1 to IFD" = Table.ReplaceValue(#"Rev Purpose - cleanning","IFD1","IFD",Replacer.ReplaceText,{"Rev Purpose"}),
    #"Rev Purpose - IFR1 to IFR" = Table.ReplaceValue(#"Rev Purpose - IFD1 to IFD","IFR1","IFR",Replacer.ReplaceText,{"Rev Purpose"}),
    #"Not in PDB - merge query 1" = Table.NestedJoin(#"Rev Purpose - IFR1 to IFR",{"Document Reference"},#"MASTER PDB TEMP",{"Document Reference"},"MASTER PDB",JoinKind.LeftOuter),
    #"Not in PDB - merge query 2" = Table.ExpandTableColumn(#"Not in PDB - merge query 1", "MASTER PDB", {"Document Reference"}, {"MASTER PDB.Document Reference"}),
    #"Not in PDB - rename" = Table.RenameColumns(#"Not in PDB - merge query 2",{{"MASTER PDB.Document Reference", "Not in PDB"}}),
    #"Not in PDB - filter on null" = Table.SelectRows(#"Not in PDB - rename", each ([Not in PDB] = null)),
    #"Not in PDB - remove" = Table.RemoveColumns(#"Not in PDB - filter on null",{"Not in PDB"}),
    #"Dates - unpivot" = Table.Unpivot(#"Not in PDB - remove", {"Actual Date", "Forecast Date", "Planned Date - revised", "Planned Date"}, "Attribute", "Value"),
    #"Status Date et Date - rename" = Table.RenameColumns(#"Dates - unpivot",{{"Attribute", "Status Date"}, {"Value", "Date"}}),


    #"Doc Type - adding" = Table.AddColumn(#"Status Date et Date - rename", "Doc type", each if Text.Contains([Document Reference], "CN") then "CN" else if Text.Contains([Document Reference], "DI") then "DI" else if Text.Contains([Document Reference], "DW") then "DW" else if Text.Contains([Document Reference], "EL") then "EL" else if Text.Contains([Document Reference], "FOR") then "FOR" else if Text.Contains([Document Reference], "IRC") then "IRC" else if Text.Contains([Document Reference], "ITP") then "ITP" else if Text.Contains([Document Reference], "JSD") then "JSD" else if Text.Contains([Document Reference], "JSM") then "JSM" else if Text.Contains([Document Reference], "JSS") then "JSS" else if Text.Contains([Document Reference], "ML") then "ML" else if Text.Contains([Document Reference], "MR") then "MR" else if Text.Contains([Document Reference], "MS") then "MS" else if Text.Contains([Document Reference], "MTO") then "MTO" else if Text.Contains([Document Reference], "NM") then "NM" else if Text.Contains([Document Reference], "PDS") then "PDS" else if Text.Contains([Document Reference], "PID") then "PID" else if Text.Contains([Document Reference], "PFD") then "PFD" else if Text.Contains([Document Reference], "PLG") then "PLG" else if Text.Contains([Document Reference], "PP") then "PP" else if Text.Contains([Document Reference], "RT") then "RT" else if Text.Contains([Document Reference], "SOW") then "SOW" else if Text.Contains([Document Reference], "SP") then "SP" else if Text.Contains([Document Reference], "SR") then "SR" else if Text.Contains([Document Reference], "STD") then "STD" else if Text.Contains([Document Reference], "TBT") then "TBT" else if Text.Contains([Document Reference], "DQP") then "DQP" else if Text.Contains([Document Reference], "STC") then "STC" else if Text.Contains([Document Reference], "TDS") then "TDS" else if Text.Contains([Document Reference], "DD") then "DD" else if Text.Contains([Document Reference], "DR") then "DR" else if Text.Contains([Document Reference], "GEA") then "GEA" else if Text.Contains([Document Reference], "LIS") then "LS" else if Text.Contains([Document Reference], "PQ") then "PQ" else if Text.Contains([Document Reference], "WI") then "WI" else null),
    #"OC - Adding" = Table.AddColumn(#"Doc Type - adding", "OC", each null),
    #"Module - adding" = Table.AddColumn(#"OC - Adding", "Module", each "ENGINEERING")
in
    #"Module - adding"

Any idea...?

Regards,

CR

Stachu · ‎09-20-2018

table 2 is dependant on table 1
in step

 #"Not in PDB - merge query 1"

you do join with MASTER PDB TEMP
I'd suggest creating reference to MASTER PDB TEMP and appending Table 2 to the new, referenced query

Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

View solution in original post

mahesh_s · ‎03-15-2022

Dear community members,

I am trying to implement incremental refresh "manually" as my database is SAP BW and query folding is not working. Here is what I am trying to do.

1. Extract latest date from Master Data table and store it as a query (lets call it last_date)
2. Use last_date query to fetch New Data (from last_date till today). It is working so fine so far.
3. Append New Data to Master Data -> Getting cyclic reference error here

Tried creating reference to Master Data and appending New Data to it. It resolves the error but it is not useful since the "last_date" query is still linked to old Master Data.

Kindly help!
@Stachu @CR

Stachu · ‎09-20-2018

table 2 is dependant on table 1
in step

 #"Not in PDB - merge query 1"

you do join with MASTER PDB TEMP
I'd suggest creating reference to MASTER PDB TEMP and appending Table 2 to the new, referenced query

Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

CR · ‎09-20-2018

indeed, it is, I understand now why it doesn't work.

So yes, I need a new query. Let's go !

Thanks,

CR

Append 2 tables - expression.error: a cyclic reference was encountered during evaluation

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024

How to Get Your Question Answered Quickly