how to get dates (dd/mm/yyyy) through API REST?

[LuisFelipeTEN95]

Hello everyone, We have linked our production data colletion system with Power BI through API Rest.

The only information that in the API REST do not have is the "date", so I'm not able to link and use the filters by date and all the dashboard that we are currently using. This database today we are exporting to excel weekly, but we now would like to have update in real time that why we are trying to connect through API REST.

The API rest has only the initial date (inside the link) as 2023-01-01 06:00:00 but no end date that should work with the "Data Segmentation"

link: "api/super/cell-stat-grid-MAIN-PROC-CURS_FromToNOW/12/2023-01-01%2006:00:00"

Do you guys have faced something like this? How to link or create the date between the database?

[rubayatyasmin]

Hi, @LuisFelipeTEN95 

While the process of creating a date might be complex, depending on your specific API and data architecture, here are some general steps that may be applicable to your situation:

1. Data Transformation: Once you have retrieved the data from your API, you can perform some transformations on your data in Power BI. If your data does not include a date but you know the data was collected at a certain frequency (e.g., hourly, daily), you might be able to generate a date/time column based on the sequence of the data and the initial date/time.

2. Use Power Query: Power BI has a very powerful data manipulation feature called Power Query. You can use this to generate a series of dates between a start date (the one you have) and an end date (the current date or "now").

   Here is an example of a M code that generates a list of dates from a start date to the current

let
StartDate = #date(2023, 1, 1),
EndDate = DateTime.LocalNow(),
NumberOfDates = Duration.Days(EndDate - StartDate)+1,
Dates = List.Dates(StartDate, NumberOfDates, #duration(1, 0, 0, 0)),
#"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}})
in
#"Renamed Columns"

3. Create a Data Table: Once you have your date series, you can create a new table in Power BI. This table can be linked to your API data using Power BI's relationship model. If you know the frequency of your data (for example, data is captured every 12 hours), you can relate this data to your generated date table.

4.Manipulate API endpoint: If your API allows it, you might be able to programmatically adjust the end date in your API endpoint to pull the data till the current date and time, or any specific date and time.

here is a similar thread. Solved: Date formatting issues MM/DD/YYYY --> DD/MM/YYYY - Microsoft Fabric Community

[LuisFelipeTEN95]

Hi, @rubayatyasmin ,

I have created a query just for data table transformation and it's running well for whole application.

let
/* CRIANDO VARIAVEIS */

Tabela = List.Buffer(#"DB_KPI"[DATA]),
MinDate = List.Min( Tabela ),
MaxDate = List.Max( Tabela ),
MaxYear = Date.Year ( MaxDate ),
MinYear = Date.Year ( MinDate ),
InitialDate = #date( MinYear, 1, 1 ),
EndDate = #date( MaxYear, 12, 31 ),

Duration = Duration.Days(EndDate - InitialDate) + 1,

Fonte = List.Dates(InitialDate, Duration, #duration(1, 0, 0, 0)),
#"Convertido para Tabela" = Table.FromList(Fonte, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Tipo Alterado" = Table.TransformColumnTypes(#"Convertido para Tabela",{{"Column1", type date}}),
#"Colunas Renomeadas" = Table.RenameColumns(#"Tipo Alterado",{{"Column1", "Data"}}),
#"Ano Inserido" = Table.AddColumn(#"Colunas Renomeadas", "Ano", each Date.Year([Data]), Int64.Type),
#"Nome do Mês Inserido" = Table.AddColumn(#"Ano Inserido", "Nome do Mês", each Date.MonthName([Data]), type text),
#"Mês Inserido" = Table.AddColumn(#"Nome do Mês Inserido", "Mês", each Date.Month([Data]), Int64.Type),
#"Dia Inserido" = Table.AddColumn(#"Mês Inserido", "Dia", each Date.Day([Data]), Int64.Type),
#"Nome do Dia Inserido" = Table.AddColumn(#"Dia Inserido", "Nome do Dia", each Date.DayOfWeekName([Data]), type text),
#"Trimestre Inserido" = Table.AddColumn(#"Nome do Dia Inserido", "Trimestre", each Date.QuarterOfYear([Data]), Int64.Type),
#"Prefixo Adicionado" = Table.TransformColumns(#"Trimestre Inserido", {{"Trimestre", each "T" & Text.From(_, "pt-BR"), type text}}),
#"Coluna Personalizada Adicionada" = Table.AddColumn(#"Prefixo Adicionado", "Personalizar", each Text.Combine({Text.Start(Text.Proper([Nome do Mês]), 3), " ", Text.From([Ano], "pt-BR")}), type text),
#"Coluna Personalizada Adicionada1" = Table.AddColumn(#"Coluna Personalizada Adicionada", "Ano Mes Num", each Text.Combine({Text.From([Ano], "pt-BR"), Text.PadStart(Text.From([Mês], "pt-BR"), 2, "0")}), type text),
#"Colunas Renomeadas1" = Table.RenameColumns(#"Coluna Personalizada Adicionada1",{{"Personalizar", "Mes e Ano"}}),
#"Tipo Alterado1" = Table.TransformColumnTypes(#"Colunas Renomeadas1",{{"Ano Mes Num", Int64.Type}}),
#"Semana do Ano Inserida" = Table.AddColumn(#"Tipo Alterado1", "Semana do Ano", each (Date.WeekOfYear([Data])), Int64.Type),
#"Prefixo Inserido" = Table.AddColumn(#"Semana do Ano Inserida", "Prefixo", each "W" & Text.From([Semana do Ano], "pt-BR"), type text),
#"Colunas Renomeadas2" = Table.RenameColumns(#"Prefixo Inserido",{{"Prefixo", "Semana"}, {"Mês", "Mês"}, {"Nome do Mês", "Nome do Mês"}}),
#"Coluna Personalizada Adicionada2" = Table.AddColumn(#"Colunas Renomeadas2", "Personalizar", each Text.Combine({Text.Start(Text.From([Ano]), 4), "-", Text.PadStart(Text.From([Semana do Ano], "pt-BR"), 2,"0")}), type text),
#"Colunas Renomeadas3" = Table.RenameColumns(#"Coluna Personalizada Adicionada2",{{"Personalizar", "ANO-SEMANA"}})

in
#"Colunas Renomeadas3"

Our data from API is updated each hour from the production mobile tablets (around 100 tablets). 
But only with the starting date, because when we tried also to set in the link the end date didn't work with the "Data Segmentation". The visuals inside the report in Power Bi remains always the same (all the information remains from 01-01-2023 to today). I'm trying to filter something that there is no date in the database.

Thanks again for the support