Cyclic error Table.AddColumn reference

[simondpalmer]

Hi,

I have cyclic error due to my Table.AddColumn(#"Promoted Headers".... ). I want to just add columns "GeoLocationLat" and "GeoLocationLong" but they are refering to "Promoted Headers". How do I just add these columns to the table?

let
    Source = Excel.Workbook(File.Contents("C:\Users\spalmer\Documents\BIM Management\SDAnalytics\SDAnalytics.xlsm"), null, true),
    SDAnalytics_Sheet = Source{[Item="SDAnalytics",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(SDAnalytics_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"CollectionDate", type datetime}, {"CollectionTime", type datetime}, {"RevitEvent", type text}, {"RevitEventDuration", type text}, {"UserName", type text}, {"ComputerAvailableHDgigs", type number}, {"ComputerAvailableRAMmbs", Int64.Type}, {"ComputerWirelessEnabled", type logical}, {"ComputerTempFileCount", Int64.Type}, {"RevitDynamoOpenings", Int64.Type}, {"RevitC4RBuildNumber", type text}, {"RevitAcceleratorBuildNumber", type text}, {"RevitDesktopConnectorBuildNumber", type text}, {"RevitVersionName", type text}, {"RevitBuildNumber", type text}, {"RevitFileWorksharingEnabled", type logical}, {"RevitFileIsCentralFile", type logical}, {"RevitFileIsDetached", type logical}, {"RevitFilePath", type text}, {"RevitFileName", type text}, {"RevitFileDateCreated", type datetime}, {"RevitFileDateModified", type datetime}, {"RevitFileSizeKbs", Int64.Type}, {"RevitFileNumOfRVTLinks", Int64.Type}, {"RevitFileNumOfCADLinks", Int64.Type}, {"RevitFileNumOfCADImports", Int64.Type}, {"RevitFileNumOfDesignOptionSets", Int64.Type}, {"RevitFileNumOfDesignOptions", Int64.Type}, {"RevitFileNumOfViews", Int64.Type}, {"RevitFileNumOfReferencePlanes", Int64.Type}, {"RevitFileNumOfFilledRegionInstances", Int64.Type}, {"RevitFileNumOfWarnings", Int64.Type}, {"Last", Int64.Type}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"RevitEventDuration", type number}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type1", "RevitType", each if Text.StartsWith([RevitFilePath], "BIM 360:") then "BIM360" else "File Server"),
    #"Added Conditional Long Column" = Table.AddColumn(#"Promoted Headers", "GeoLocationLong", each if Text.StartsWith([RevitFilePath], "\\sdlavfs1") then "-118.3965" else if Text.StartsWith([RevitFilePath], "\\sdocpsrv1") then "33.7175" else if Text.StartsWith([RevitFilePath], "\\sdsbvfs1") then "-119.6982" else null),
    #"Ädded Conditional Lat Column" = Table.AddColumn(#"Promoted Headers", "GeoLocationLat", each if Text.StartsWith([RevitFilePath], "\\sdlavfs1") then "34.0211" else if Text.StartsWith([RevitFilePath], "\\sdsbvfs1") then "34.4208" else if Text.StartsWith([RevitFilePath], "\\sdocpsrv1") then "33.7175" else null)
in  #"SD Analytics"

[v-alq-msft]

Hi, @simondpalmer 

Step #"Added Conditional Long Column" should refer to the last step #"Added Conditional Column". While step #"Ädded Conditional Lat Column" need to refer to step #"Added Conditional Long Column". Finally you need to output step #"Ädded Conditional Lat Column".

let
    Source = Excel.Workbook(File.Contents("C:\Users\spalmer\Documents\BIM Management\SDAnalytics\SDAnalytics.xlsm"), null, true),
    SDAnalytics_Sheet = Source{[Item="SDAnalytics",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(SDAnalytics_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"CollectionDate", type datetime}, {"CollectionTime", type datetime}, {"RevitEvent", type text}, {"RevitEventDuration", type text}, {"UserName", type text}, {"ComputerAvailableHDgigs", type number}, {"ComputerAvailableRAMmbs", Int64.Type}, {"ComputerWirelessEnabled", type logical}, {"ComputerTempFileCount", Int64.Type}, {"RevitDynamoOpenings", Int64.Type}, {"RevitC4RBuildNumber", type text}, {"RevitAcceleratorBuildNumber", type text}, {"RevitDesktopConnectorBuildNumber", type text}, {"RevitVersionName", type text}, {"RevitBuildNumber", type text}, {"RevitFileWorksharingEnabled", type logical}, {"RevitFileIsCentralFile", type logical}, {"RevitFileIsDetached", type logical}, {"RevitFilePath", type text}, {"RevitFileName", type text}, {"RevitFileDateCreated", type datetime}, {"RevitFileDateModified", type datetime}, {"RevitFileSizeKbs", Int64.Type}, {"RevitFileNumOfRVTLinks", Int64.Type}, {"RevitFileNumOfCADLinks", Int64.Type}, {"RevitFileNumOfCADImports", Int64.Type}, {"RevitFileNumOfDesignOptionSets", Int64.Type}, {"RevitFileNumOfDesignOptions", Int64.Type}, {"RevitFileNumOfViews", Int64.Type}, {"RevitFileNumOfReferencePlanes", Int64.Type}, {"RevitFileNumOfFilledRegionInstances", Int64.Type}, {"RevitFileNumOfWarnings", Int64.Type}, {"Last", Int64.Type}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"RevitEventDuration", type number}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type1", "RevitType", each if Text.StartsWith([RevitFilePath], "BIM 360:") then "BIM360" else "File Server"),
    #"Added Conditional Long Column" = Table.AddColumn(#"Added Conditional Column", "GeoLocationLong", each if Text.StartsWith([RevitFilePath], "\\sdlavfs1") then "-118.3965" else if Text.StartsWith([RevitFilePath], "\\sdocpsrv1") then "33.7175" else if Text.StartsWith([RevitFilePath], "\\sdsbvfs1") then "-119.6982" else null),
    #"Ädded Conditional Lat Column" = Table.AddColumn(#"Added Conditional Long Column", "GeoLocationLat", each if Text.StartsWith([RevitFilePath], "\\sdlavfs1") then "34.0211" else if Text.StartsWith([RevitFilePath], "\\sdsbvfs1") then "34.4208" else if Text.StartsWith([RevitFilePath], "\\sdocpsrv1") then "33.7175" else null)
in  #"Ädded Conditional Lat Column"

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PhilipTreacy · Accepted solution]

Hi @simondpalmer 

Why aren't you referring to the previous step in the last 2 steps when you add the Lat and Long columns, like this:

#"Added Conditional Long Column" = Table.AddColumn(#"Added Conditional Column", "GeoLocationLong", each if Text.StartsWith([RevitFilePath], "\\sdlavfs1") then "-118.3965" else if Text.StartsWith([RevitFilePath], "\\sdocpsrv1") then "33.7175" else if Text.StartsWith([RevitFilePath], "\\sdsbvfs1") then "-119.6982" else null),
    #"Ädded Conditional Lat Column" = Table.AddColumn(#"Added Conditional Long Column", "GeoLocationLat", each if Text.StartsWith([RevitFilePath], "\\sdlavfs1") then "34.0211" else if Text.StartsWith([RevitFilePath], "\\sdsbvfs1") then "34.4208" else if Text.StartsWith([RevitFilePath], "\\sdocpsrv1") then "33.7175" else null)

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.