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.
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.
Solved! Go to 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.
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsMFelix,
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.
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.
User | Count |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |