cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Allan77R2V1 Regular Visitor
Regular Visitor

Adding a data colmun when loading CSV files with JSON

 

I am using json to pull the CSV files for the data on my monthly Azure summary billing.  Below is what it looks like before I import the CSV files. You can see I have a row for each month of the year and I have several years of billing that I pull in with each load.

Image 1.png

 

The issue I have is that when I load the CSV files I end with the data below (i am just showing the first column). These rows are repeaded for every month. The issue is that that the CSV files do not contain the data for the month and year of the CSV.  The Azure Service Commitment does have the Month but no year.  THe Report Generation Date is all over the place and I believe its a date that represents if Microsoft made changes and so it can vary by many months, by example the last  4 months all have a report generation date of Jan 1 2018.

 

Is there any way that when importing the CSV that I some how keep the Column1.Month in the above pic for each of the rows for that CSV file? Does anyone have other suggestions?

 

Image 2.png

1 ACCEPTED SOLUTION

Accepted Solutions
Allan77R2V1 Regular Visitor
Regular Visitor

Re: Adding a data colmun when loading CSV files with JSON

I ended being able to use some logic to get the date from the Month or the report and the Report Generation Date.

4 REPLIES 4
Rfranca Member
Member

Re: Adding a data colmun when loading CSV files with JSON

@Allan77R2V1

 


This is information about the files you need, such as 'Date accessed', 'Date created' and 'Date modified'
To obtain this information you must obtain data through the FOLDER option

Or use the POWER QUERY M language functions.

 

Clipboard012.gif

Allan77R2V1 Regular Visitor
Regular Visitor

Re: Adding a data colmun when loading CSV files with JSON

Im not sure if Im clear on what your suggesting. In the first pic in my orginal post I have the dates of the files and link to the CSV files but when I import the CSV that date column is removed and I end up wth the second picture.

Rfranca Member
Member

Re: Adding a data colmun when loading CSV files with JSON

@Allan77R2V1

 

Can you provide an example json file?

Or Please try and test using it.

 

http://community.powerbi.com/t5/Integrations-with-Files-and/An-json-example/m-p/57447

 


Best Regards,
Rfranca

Allan77R2V1 Regular Visitor
Regular Visitor

Re: Adding a data colmun when loading CSV files with JSON

I ended being able to use some logic to get the date from the Month or the report and the Report Generation Date.