Hi all. Our business recently started using Uber for Business for corporate travel. Uber provides a monthly CSV for reporting which i’ve just looked at. I was hoping to use Power BI to create a management report. The CSV is quite ugly TBH and requires significant data transformation. Simple things like co-ordinates to create a nice map visual aren’t there. Has anyone else tried to create anything similar?
PowerQuery is great at taking multiple CSV files and mashing them all together. Any chance you can put a sample csv up?
Thanks Nick. The idea would be to have one Power BI File which we’d import the csv each month.Uber Sample.txt (7.5 KB)
You weren’t kidding about this being ugly. I took a quick look, but can be done. Might just take a couple of days. Happen to have report of that data and where it goes?
-Nick
Hi Nick
Not sure I understand. I’ll have an employee master table and cost centre. We’ll use that to report the trips to the divisional manager for sign off/information purposes if that is what you’re meaning?
@Halfa_bee,
That was my bad, I was pretty unclear. I was just looking to see if you had a formatted version of the csv file, but I went another way.
So there’s a few (ok, probably more than a few things going on in this file).
- There is a parameter where you will enter in the folder location of all the csv files.
- There are two functions. One that will Rename Columns and then one that does all the transformations
- There is a list of column names, which is used in the function to Rename Columns
- Since we are importing all the files from a folder you will see the following:
in the table ( dont do anything with the function) you will see in the applied steps that it calls the fnTransform function. This function will do all the transformations in this sample file, which then will show up in, what I call, the final table. Which is all the csv files combined in the correct format. So make any further transformations in the Sample File, except setting data types which has to be done in the final table.
Here’s a screenshot of the final table, well at least part of it. I also thought you may want the Report Date which was in the csv file (for time intelligence and what not) so added that in there. I just added a few copies of the sample text file to ensure it all worked and looks like it does:
Here’s the pbix file:
Combing CSV Files.pbix (22.7 KB)
-Nick
1 Like
Hi Nick
Many many thanks. This is fantastic. Works perfectly. Attempting to follow the logic slowly as i’m Still learning. What can be achieved with Power BI is just fantastic.
Much appreciated.
@Halfa_bee
Yes! Pretty much anything (within reason) can be done with these tools. We are always learning , so just take it one step at a time and it will eventually make sense 
Hi Nick
Getting my head around the steps. Just clarifying the reason for the need for list function? Also trying to find how to complete the final step called “custom1” which converts the column headers to list.
Cheers
@Halfa_bee
In the CSV files there were some extra commas in the fields ( I believe in Country, State) that were not present in the column headers. So if I just split the entire file by comma ( which would have been much easier) columns would not line up because the column header was only split once, while the values were split a few times. So instead of splitting one by one and seeing if I could merge some things ( which is how I started and then thought better of it) I took the file and stripped out all the data except the column headers and transformed into a list. Which became the ColumnHeaders list you see.
Then back in the CSV files with the data, I removed everything except the actual data. Then I was able to split everything by a comma. But since I took out the column names, everything (after the split) was just a generic name. Then from there I was able to use the RenameColumns function to take the column names list, and overwrite those generic column names, but now they would line up.
That can be a little confusing I know. So I attached an update pbix file, but instead of seeing just the fnTransform called in the “Transform Sample File from Query 1”, I put in all the actual steps so you can click in and see what is going on.
Combing CSV Files.pbix (23.0 KB)
Thanks Nick. Explains it perfectly
Many thanks