Forum Discussion
Solved
Cyclic error Table.AddColumn reference
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"
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.
2 Replies
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.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.
