Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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"
Solved! Go to Solution.
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.
Proud to be a Super User!
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.
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.
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.