Border Crossing Report

Border Crossing Report

 

Summary

The Bureau of Transportation Statistics (BTS) provides summary statistics on inbound border crossings between the United States, Canada, and Mexico at the port level. This data covers a wide range of categories like trucks, trains, containers, buses, personal vehicles, passengers, and pedestrians.
 

Objective

As part of the participation in DATDATA GAME Edition 5 - Developing a Dashboard with Real Data, I've created a dashboard that collects data on border crossings at ports of entry overseen by U.S. Customs and Border Protection (CBP). This data accurately represents the number of vehicles, containers, passengers, and pedestrians entering U.S. territory.
The information is extracted via an O-DATA connection and processed in PowerBI, where the resulting dashboard is generated. Designed with simplicity and user-friendliness in mind, the dashboard even includes tooltips to provide specific details about the overall data.
 

Step to step

Step 1: ODATA Connection
In this step, we establish an ODATA connection to a specific URL (https://data.bts.gov/Research-and-Statistics/Border-Crossing-Entry-Data/keg4-3bc2/data). This connection allows us to access the required data for our project.
Once the connection is established, we perform several transformations on the data to cleanse and format it properly. This includes removing unnecessary columns, renaming existing ones for clarity, and ensuring the data types are correct for our needs.
Below is the code used for these transformations:
= Table.RemoveColumns(Origen, {"__id"}) = Table.RenameColumns(#"Columns removed", {{"port_code", "Port Code"}, {"port_name", "Port Name"}, {"point", "Location"}, {"measure", "Measure"}, {"date", "Date"}, {"border", "Border"}, {"value", "Value"}, {"longitude", "Longitude"}, {"latitude", "Latitude"}, {"state", "State"}})
By the end of this step, we have a cleaned and well-structured data set that can serve as a reference for the remainder of the project.
 
Step 2: Generation of Auxiliary Tables
Description: In this step, I create auxiliary tables derived from the initial connection. These tables form a comprehensive data model that we will use in the subsequent stages of our project.
notion image
 
Step 3: Cleaning of Auxiliary Tables
In this step, we perform a series of transformations to clean up each auxiliary table. This involves selecting specific columns, renaming them for clarity, and ensuring the data types are correct for our needs. We also use functions such as Table.Distinct and Table.TransformColumns to refine our data. The code snippets below detail the specific transformations done for the "Puertos", "Modo Entrada", and "Conteo de Entradas" tables.
A. “Puertos” Table
= Border_Crossing_Entry_Data = Table.SelectColumns(Origen,{"Port Name", "State", "Port Code", "Border", "Location"}) = Table.Distinct(#"Otras columnas quitadas", {"Port Code"}) = Table.TransformColumns(#"Duplicados quitados", {{"Location", each Text.BetweenDelimiters(_, "(", ")"), type text}}) = Table.SplitColumn(#"Texto extraído entre delimitadores", "Location", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Latitude", "Longitude"}) = Table.ReplaceValue(#"Dividir columna por delimitador"," Border","", Replacer.ReplaceText,{"Border"}) = Table.TransformColumnTypes(#"Valor reemplazado",{{"Port Code", Int64.Type}, {"Longitude", type number}, {"Latitude", type number}}) = Table.RenameColumns(#"Tipo cambiado",{{"Longitude", "Latitud"}, {"Latitude", "Longitud"}, {"Border", "Frontera"}, {"Port Code", "ID Puerto"}, {"Port Name", "Nombre Puerto"}, {"State", "Estado"}}) = Table.ReplaceValue(#"Columnas con nombre cambiado","US-Canada","CANADA", Replacer.ReplaceText,{"Frontera"}) = Table.ReplaceValue(#"Valor reemplazado1","US-Mexico","MEXICO", Replacer.ReplaceText,{"Frontera"})
B. “Modo Entrada” Table
= Border_Crossing_Entry_Data = Origen[Measure] = List.Distinct(Measure1) = Table.FromList(#"Duplicados quitados", Splitter.SplitByNothing(), null, null, ExtraValues.Error) = Table.TransformColumnTypes(#"Convertida en tabla",{{"Column1", type text}}) = Table.RenameColumns(#"Tipo cambiado",{{"Column1", "Mode"}}) = Table.AddIndexColumn(#"Columnas con nombre cambiado", "Mode Code", 1, 1, Int64.Type) = Table.RenameColumns(#"Índice agregado",{{"Mode", "Modo"}, {"Mode Code", "ID Modo"}})
C. “Conteo de Entradas” Table
= Border_Crossing_Entry_Data = Table.NestedJoin(Origen, {"Measure"}, #"Modo Entrada", {"Modo"}, "Modo Entrada", JoinKind.LeftOuter) = Table.ExpandTableColumn(#"Consultas combinadas", "Modo Entrada", {"ID Modo"}, {"ID Modo"}) = Table.SelectColumns(#"Se expandió Crossing Mode",{"Port Code", "Date", "Value", "ID Modo"}) = Table.TransformColumns(#"Otras columnas quitadas", {{"Date", each Text.BeforeDelimiter(_, "T"), type text}}) = Table.TransformColumnTypes(#"Texto extraído antes del delimitador", {{"Port Code", Int64.Type}, {"Value", Int64.Type}, {"Date", type date}}) = Table.RenameColumns(#"Tipo cambiado",{{"Value", "Conteo Entradas"}})
 
Step 4: Creation of Measures
In this step, I set up a series of measures. These measures are critical for our dashboard as they provide calculated values based on our data that we can use in our visualizations.
For instance, the "Descripcion" measure uses a SWITCH function to provide different descriptions based on the selected border. The "Titulo" measure creates a title for our visualization that dynamically changes based on the selected border.
Other measures, like "Total Entradas", "Total entradas Canada", "Total entradas México", "Total entradas Personas", and "Total entradas Vehículo", calculate the total number of entries for different categories. These are crucial for providing insights on the data.
Lastly, the "Vacio" measure is used for information fields within the dashboard. This measure simply returns a blank space (" "), which can be useful for formatting and visual appeal in the dashboard.
It's important to remember that these measures are dynamic and will update based on the data and any filters or slicers that are applied in the dashboard.
A. “Descripcion” Measure
Descripcion = SWITCH ( TRUE (), ISBLANK (SELECTEDVALUE (Puertos[Frontera])), "Border crossing data for trucks, trains, containers, buses, personal vehicles, passengers and pedestrians at the U.S.-Canada and Mexico borders from 1996 to present.", SELECTEDVALUE (Puertos[Frontera]) = "Canada", "Data from 89 border crossings of trucks, trains, containers, buses, personal vehicles, passengers and pedestrians at the U.S.-Canada borders from 1996 to the present.", SELECTEDVALUE (Puertos[Frontera]) = "Mexico", "Data from the 28 border crossings of trucks, trains, containers, buses, personal vehicles, passengers and pedestrians at the U.S.-Mexico borders from 1996 to the present.", "Border crossing data for trucks, trains, containers, buses, personal vehicles, passengers and pedestrians at the U.S.-Canada and Mexico borders from 1996 to present." )
B. “Titulo” Measure
Titulo = VAR frontera = SELECTEDVALUE(Puertos[Frontera]) RETURN IF(ISBLANK(frontera), "ENTRIES TO THE U.S. THROUGH THE MEXICO AND CANADA BORDER", "ENTRIES TO THE US THROUGH " & frontera & " BORDER")
C. “Titulo Entrada” Measure
Titulo Entrada = VAR frontera = SELECTEDVALUE(Puertos[Frontera]) RETURN (" that entered from " & frontera)
D. “Titulo Mes” Measure
Titulo Mes = VAR anio = SELECTEDVALUE(Calendario[Año]) RETURN ("Entries in months of " & anio)
E. “Titulo Puerto” Measure
Titulo Puerto = VAR puerto = SELECTEDVALUE(Puertos[Frontera]) VAR nombre = SELECTEDVALUE(Puertos[Nombre Puerto]) RETURN ( nombre & ", border with " & puerto)
F. “Total Entradas” Measure
Total Entradas = IF(ISBLANK(SUM('Conteo Entradas'[Conteo Entradas])), 0, SUM('Conteo Entradas'[Conteo Entradas]))
G. “Total entradas Canada” Measure
Total entradas Canadá = CALCULATE( 'Medidas'[Total Entradas], KEEPFILTERS( 'Puertos'[Frontera] = "CANADÁ" ) )
H. “Total entradas México” Measure
Total entradas México = CALCULATE( 'Medidas'[Total Entradas], KEEPFILTERS( 'Puertos'[Frontera] = "MÉXICO" ) )
I. “Total entradas Personas” Measure
Total entradas Personas = IF( ISBLANK( CALCULATE( 'Medidas'[Total Entradas], KEEPFILTERS( 'Modo Entrada'[Categoría Modo] = "Personas" ) ) ), 0, CALCULATE( 'Medidas'[Total Entradas], KEEPFILTERS( 'Modo Entrada'[Categoría Modo] = "Personas" ) ) )
J. “Total entradas Vehiculos” Measure
Total entradas Vehículo = IF( ISBLANK( CALCULATE( 'Medidas'[Total Entradas], KEEPFILTERS('Modo Entrada'[Categoría Modo] = "Vehículos") ) ), 0, CALCULATE( 'Medidas'[Total Entradas], KEEPFILTERS('Modo Entrada'[Categoría Modo] = "Vehículos") ) )
K. “Vacio” Measure
It is used for information fields within the dashboard.
Vacio = " "
 
5.- Creation of Tooltip
For a more interactive and informative user experience, tooltips are created. These tooltips show specific details when a user hovers over a certain part of the dashboard. This includes information on the number of entries for each mode of transportation, border crossing, and year.
 
6.- Creation of DashBoard
In this final step, I create the dashboard using the data model, measures, and tooltips I've prepared. I design the dashboard to be user-friendly, informative, and visually appealing. With the use of multiple charts and graphs, I represent the data in a way that's easy to understand and interpret. The dashboard also includes interactive features, allowing users to filter and view specific data based on their interests. This provides a comprehensive overview of the border crossing data.