How can I combine different CSV files ( with different shemas ) in my dataflow creation

[Vrykolakas]

Hi All !

I'm struggling with this task currently during my Dataflow creation in my pbi service.

So I have a few csv files ( They all contain how many App installs have been - on a daily basis - in each country ), They are stored in a Sharepoint Folder.

• File1.csv is structured as followed " Date |  All Country installations | Country 1 installations | ..... | Country 9 installations "
• File2.csv is structured as followed " Date | Country 10 installations | Country 11 installations | ..... | Country 15 installations "
• and the upcoming files also obey the same logic, starting with the column " Date " followed by other countries..

The point is to combine these files, and do some reporting.. But Here is where I feel lost. 

I would really appreciate your guidance, to help me find an optimal way to digest these files.

Thanks !

[Anonymous]

Hi @Vrykolakas ,

Looks like merge queries.

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-shape-and-combine-data#combine-querie... .

The point is will the [All Country installations] changed after the tables being merged?

If the [All Country installations] is the sum of country installations, you may need to add a custom column after the tables being merged.

Best Regards,

Jay

[ibarrau · Accepted solution]

Hi. Assuming you have one date column and a lot of countries. You might need load each csv manually, because it doesn't look like a combine or append solution. It sounds like a merge/join one. The example data makes me think you need to match files by date and one you have all the countries, unpivot the countries to build a country column and a value for their values.

I hope that helps,

[Vrykolakas]

Thank you @ibarrau for your helpful comment !

I currently dispose of these two csv files : ( But more files will be added in the future, each with the Date column, and different countries ( I should also take care of the possibility that some countries might be redundant.. and a "Note" column, that should be deleted in the process ) :

So the option of merging two Queries, might not be very optimal, since I need to Query each new added csv file in my sharepoint..

Is there a way to unify the structure ( in the Tranformation file part ), by transposing all the country fields into one Column " Country " and another one for the " Installs" values ? Because When transforming one file, the other file shows up an error, since the columns don't match.. How can I fix this part ?

@Anonymous, Hi ! Thank you for your comment, I think this also answers your question, and gives better explanation as my initial post 🙂