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. 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 posthelps, then please considerAccept it as the solutionto 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!