Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AmberJ217
Frequent Visitor

New data in csv / sharepoint online list source not picked up in the merge query

I connect to our compnay api to bring in various data to the model and connect to Sharepoint online lists as another source. The sharepoint lists are manually updated with more 'clean' data regularly. For example, I want the company name result from the api to be cleaned to remove multiple variations of the same company on a visual, i.e. "mcdonalds", "Mcdonalds", "McDonald" from the api should match to "McDonald's Corporation". This link is created within the sharepoint list with a 'From' (housing the examples above) and a 'To' column (all saying McDonald's Corporation).

 

I created this link in PowerQuery with a merge query between the Company column from the api and the Clean Company sharepoint list. I have made sure to trim and clean both columns from the api data and the sharepoint list and am linking the 'from' column , not the 'to' in the Clean Company list. Merged via JoinKind.LeftOuter.

 

Upon updating the lists recently I noticed the company names with no 'clean' results, aka the ones I would usually add to the sharepoint list, are remaining with no match after refreshing the model. I am allowing time for the Sharepoint list to update and save, exiting the list, hard refreshing in PowerBI to no success. I have also replicated the list in a second sharepoint list which has not worked. It led me to believe there was an issue with Sharepoint Online lists so I replicated the list in a csv, opened a blank PowerBI doc, brought in the data required from the api, added the csv file as a new source, and the number of rows in the csv matched the number of rows brought through in power query. I then added more rows to the csv, closed the doc, waited for it to save and update on sharepoint, refreshed the PowerBI, the number of rows brought through again match the number in the csv. But this time it won't make the link between the company name column and the clean company name; the data I have just added to the csv shows blank on a table visual in powerbi in the 'clean' column.

 

As the data is behaving the same way if the source is a Sharepoint Online List or is a csv, I suspect there is something wrong with the way I have approached the merge query? Or is there a limit to the number of rows I can bring in (about 2240 at the minute)?

1 ACCEPTED SOLUTION
AmberJ217
Frequent Visitor

Update for future readers: The solution was to format the text in all caps/all lowercase. 

 

I had trimmed, cleaned, removed spaces between words, but capitalised each word. Afetr being stuck on the issue for a while, changing to all caps worked for me (all lowercase would be fine too). 

Also ensure sort, buffer, remove duplicates steps are also in place!

View solution in original post

3 REPLIES 3
AmberJ217
Frequent Visitor

Update for future readers: The solution was to format the text in all caps/all lowercase. 

 

I had trimmed, cleaned, removed spaces between words, but capitalised each word. Afetr being stuck on the issue for a while, changing to all caps worked for me (all lowercase would be fine too). 

Also ensure sort, buffer, remove duplicates steps are also in place!

v-cgao-msft
Community Support
Community Support

Hi @AmberJ217 ,

 

1. Please check if the data format of the two fields is the same. Set them to the same data format, e.g. text.

 

2. Check for blanks or unprintable characters that would make them actually different. Or even better: Clean and trim those fields using Power Query's functions for that before merging, making sure they actually are identical.

 

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

Hi, thanks for the suggestion but this has not helped.

 

I had already formatted the columns the same (text) and timmed & cleaned before merging.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.