Power Query Editor Combine Multiple Columns to Create Single Date Column from bls.gov APIv2

[knotpc]

Hello, 

I am importing bls.gov data for CPI Series https://api.bls.gov/publicAPI/v2/timeseries/data/CUUR0000SA0?registrationkey=your APIKey&startyear=2000&endyear=2019.

The data is parsing correctly but, I end up with useless dates when using a Date Table that has relationships with other economic data(API's) that contain real dates. Charting data in this format is useless as Power BI will SUM all the values for 2019, 2018 etc. See example data below from actual query. 

To correct the issue, I would like to create a new Column named DATE that combines a new column called DAY, always 01 with Column1.period and Column1.year. To do this I modified the table to look like below, renaming columns and adding a column called day (each is the 1st). 

With the data in this format I now what to add a new column that combines the values in the above into a new DATE column. The end result should be for example 01/07/2019 for Row 1. My question is how to actually combine the three columns. I have tried ConcatenateX and Union both give me an error and the table will then not load. 

Any help on this one would be greatly appreciated as I have numerous tables that need modification to add a date so that they will play well with other API data sources that include complete dates. 

[MFelix]

Hi @knotpc ,

You can do this in two different ways on the query editor or on DAX, in both manners no need to add the day column to your data since one of the date parameters is the day so you can place 1 on that part of the formula.

Query Editor

Add the following column:

Column with the Day column
#date([Year] , Number.FromText([Month]) , [Day]) 

Column wihtout the need for the day column
#date([Year] , Number.FromText([Month]) , 1) 

The month part is convert to number since on your example you have it as text if all columns are number then just do the following

#date([Year] , [Month], [Day]) 

DAX

Add the following column:

Date = DATE('Table'[Year];'Table'[Mont];1)

Both should work as expected then just need to format as date.

Regards.

MFelix

[knotpc · Accepted solution]

MFelix, 

I chose to perform the conversion in Query Editor to keep things clean. Your example worked perfectly once I changed the [Year] and [Month] to Whole Numbers. 

Thanks for your help. I totally missed the ability to do the conversion this way.