Facility_Dataflow.m

let
  Source = Lakehouse.Contents([]),
  #"Navigation 1" = Source{[workspaceId = "fb8c5c0a-2620-4db1-9e75-976ad2f69b69"]}[Data],
  #"Navigation 2" = #"Navigation 1"{[lakehouseId = "57295ad7-476c-4050-80d4-def33926306d"]}[Data],
  #"Navigation 3" = #"Navigation 2"{[Id = "Files", ItemKind = "Folder"]}[Data],
  #"Navigation 4" = #"Navigation 3"{[Name = "FacilityData"]}[Content],
  #"Expanded Attributes" = Table.ExpandRecordColumn(#"Navigation 4", "Attributes", {"Content Type", "Kind", "Size", "Group", "Owner", "Permissions"}, {"Content Type", "Kind", "Size", "Group", "Owner", "Permissions"}),
  #"Filtered hidden files" = Table.SelectRows(#"Expanded Attributes", each [Attributes]?[Hidden]? <> true),
  #"Invoke custom function" = Table.AddColumn(#"Filtered hidden files", "Transform file", each #"Transform file"([Content])),
  #"Removed other columns" = Table.SelectColumns(#"Invoke custom function", {"Transform file"}),
  #"Expanded Transform file" = Table.ExpandTableColumn(#"Removed other columns", "Transform file", {"CURRENT_COUNT", "TOTAL_COUNT", "LIMIT", "OFFSET", "QUERY", "ACTIVITY", "CAMPSITE", "EVENT", "Enabled", "FACILITYADDRESS", "FacilityAccessibilityText", "FacilityAdaAccess", "FacilityDescription", "FacilityDirections", "FacilityEmail", "FacilityID", "FacilityLatitude", "FacilityLongitude", "FacilityMapURL", "FacilityName", "FacilityPhone", "FacilityReservationURL", "FacilityTypeDescription", "FacilityUseFeeDescription", "COORDINATES.0", "COORDINATES.1", "TYPE", "Keywords", "LINK", "LastUpdatedDate", "LegacyFacilityID", "MEDIA", "ORGANIZATION", "OrgFacilityID", "PERMITENTRANCE", "ParentOrgID", "ParentRecAreaID", "RECAREA", "Reservable", "StayLimit", "TOUR"}, {"CURRENT_COUNT", "TOTAL_COUNT", "LIMIT", "OFFSET", "QUERY", "ACTIVITY", "CAMPSITE", "EVENT", "Enabled", "FACILITYADDRESS", "FacilityAccessibilityText", "FacilityAdaAccess", "FacilityDescription", "FacilityDirections", "FacilityEmail", "FacilityID", "FacilityLatitude", "FacilityLongitude", "FacilityMapURL", "FacilityName", "FacilityPhone", "FacilityReservationURL", "FacilityTypeDescription", "FacilityUseFeeDescription", "COORDINATES.0", "COORDINATES.1", "TYPE", "Keywords", "LINK", "LastUpdatedDate", "LegacyFacilityID", "MEDIA", "ORGANIZATION", "OrgFacilityID", "PERMITENTRANCE", "ParentOrgID", "ParentRecAreaID", "RECAREA", "Reservable", "StayLimit", "TOUR"}),
  #"Removed columns" = Table.RemoveColumns(#"Expanded Transform file", {"CURRENT_COUNT", "TOTAL_COUNT", "LIMIT", "OFFSET", "QUERY", "FACILITYADDRESS", "Enabled", "EVENT", "CAMPSITE", "ACTIVITY", "LINK", "ORGANIZATION", "RECAREA", "TOUR", "PERMITENTRANCE", "COORDINATES.0", "COORDINATES.1"}),
  #"Expanded MEDIA" = Table.ExpandListColumn(#"Removed columns", "MEDIA"),
  #"Expanded MEDIA 1" = Table.ExpandRecordColumn(#"Expanded MEDIA", "MEDIA", {"Credits", "Description", "EmbedCode", "EntityID", "EntityMediaID", "EntityType", "Height", "IsGallery", "IsPreview", "IsPrimary", "MediaType", "Subtitle", "Title", "URL", "Width"}, {"Credits", "Description", "EmbedCode", "EntityID", "EntityMediaID", "EntityType", "Height", "IsGallery", "IsPreview", "IsPrimary", "MediaType", "Subtitle", "Title", "URL", "Width"}),
  #"Changed column type" = Table.TransformColumnTypes(#"Expanded MEDIA 1", {{"FacilityAccessibilityText", type text}, {"FacilityAdaAccess", type text}, {"FacilityDescription", type text}, {"FacilityDirections", type text}, {"FacilityEmail", type text}, {"FacilityID", type text}, {"FacilityLatitude", type number}, {"FacilityLongitude", type number}, {"FacilityMapURL", type text}, {"FacilityName", type text}, {"FacilityPhone", type text}, {"FacilityReservationURL", type text}, {"FacilityTypeDescription", type text}, {"FacilityUseFeeDescription", type text}, {"TYPE", type text}, {"Keywords", type text}, {"LastUpdatedDate", type date}, {"LegacyFacilityID", type text}, {"Credits", type text}, {"Description", type text}, {"EmbedCode", type text}, {"EntityID", type text}, {"EntityMediaID", type text}, {"EntityType", type text}, {"Height", type number}, {"IsGallery", type logical}, {"IsPreview", type logical}, {"IsPrimary", type logical}, {"MediaType", type text}, {"Subtitle", type text}, {"Title", type text}, {"URL", type text}, {"Width", type number}, {"OrgFacilityID", type text}, {"ParentOrgID", type text}, {"ParentRecAreaID", type text}, {"Reservable", type logical}, {"StayLimit", type text}}),
  #"Removed columns 1" = Table.RemoveColumns(#"Changed column type", {"EmbedCode"}),
  #"Filtered rows" = Table.SelectRows(#"Removed columns 1", each ([FacilityID] <> null))
in
  #"Filtered rows"