Recurring/Repeated values in columns while merging queries

Question

Hi All,&nbsp;Please I really need help with this. Dropbox link attached.&nbsp;I have 5 csv files that I am trying to merge into one csv file so I can export and run predictive analysis in python. I just want to get a single csv file that has Geo Name, year, Sex, Age, Immigration status, Labour status, Employment income, Work activity status, Highest Diploma, Major field of study, Occupational classification into one csv file.&nbsp;I created relationships as seen in the screenshot below. I then go to power query and use merge query . When I join the first file (Education vs immigration) to the second file (Employment income vs job type) , the values in columns that has the total number of immigrants as per each location , change and reflect a recurring value all through the whole data. I inore this and attach the third csv, all the data (employment income values ) before I merged the third data also change and become recurring. Only one value runs through the whole employment income .&nbsp;&nbsp;&nbsp;I have tried aggregate instead of expanded but it ran 9hours non stop and I had to cancel it.&nbsp; There another software or way to merge and get the desired data into one csv? This is the link please.&nbsp;&nbsp;https://www.dropbox.com/s/8rpk287lk2zcdqc/Predicting%20Success.pbix?dl=0&nbsp;

mahoneypat · Answer

This should be doable but merges can really slow things down.&nbsp; It is hard to troubleshoot your queries w/o the csv files to refresh against.&nbsp; Also, all the many:many relationships make this a real challenge.&nbsp; I also noticed at least one of the relationships was on two columns that don't have matching values.
&nbsp;
Also, you shouldn't have to export your data to csv from a visual.&nbsp; You can just use the Python integration in Power BI.
&nbsp;
Regards,
Pat
&nbsp;

anonymous · Answer

mahoneypat&nbsp;&nbsp;Thanks for reaching out. The link for my dropbox has the powerbi file .Can you help me look at it then?&nbsp;https://www.dropbox.com/s/8rpk287lk2zcdqc/Predicting%20Success.pbix?dl=0&nbsp;Thanks.

mahoneypat · Answer

The pbix file has been deleted.&nbsp; Please send a link to the csv files so that I can modify the queries.&nbsp; Also make it clear exactly how the file need to be combined.
Regards,
Pat

anonymous · Answer

@mahoneypat . Thank you.The link is below. https://www.dropbox.com/sh/hn95uj15mzp0aur/AADNjRHhco4MviULpWC3qI1La?dl=0I want to combine the columns (the DIM) for the columns. I just need the columns belowGeo Name, Year, Sex(3), Age(13), Immigration Status(4), Employment Status, Employment Income, Work Activity Status, Highest certificate Diploma(15), Major Field of Study(43), Occupational Classification (NOC)(193), Location of study(5). If you use the Power BI file, you only need the "Education vs Immigration"; "Employment Income vs Job Type"; "Income, STEM,location of study"; "Major field of study vs Skill" files. Thank you.Maria

mahoneypat · Answer

Anonymous&nbsp;I looked further at this and I am not sure you can get there from here.&nbsp; Note that I only see 2 csv files at that link, so I could not download the others.&nbsp; In any case, it looks like you are doing only simple data transformations there (promote headers, change type, etc.) which is good.&nbsp; I now see what you mean by "merge" is that you are making relationships between these very large tables on fields that have many matches between them, and trying to make a table visual from them to export the data.&nbsp; Ideally, the granularity of each table (each row is one something) is clear and 1:Many relationships can be made.&nbsp; So far, you have many:many relationships which is going to make things very challenging or impossible.&nbsp; Also note that you will only be able to export 150,000 rows from a table visual.&nbsp; Once the table you need is made in Power BI, there are other ways to get more rows out.
&nbsp;
Is it clear to you what the granularity of each table is and how they should connect to each other?&nbsp; I don't see any columns that have a unique value on each row in your tables.&nbsp; I am not sure I am going to be able to help you on this one.&nbsp; Where did you get these files?&nbsp; Is there a link to documentation about them you can send?
&nbsp;
Regards,
Pat
&nbsp;

Forum Discussion

Recurring/Repeated values in columns while merging queries

6 Replies

Related content

Merge Column

Merge duplicates data in columns

Merge columns keep null

MERGE QUERIES

Issues with Merged Column