I've come across a video on youtube that describes How to Easily Map Your Database Schema in Power BI using the AdventureWorks database from Microsoft. Now I'm trying to replicate that example using another database. The problem is that many of my columns have got similar content, but different column names with prefixes such as pk_ or fk_ depending on which tables they are located in. And that causes the following query to fail:
FROM INFORMATION_SCHEMA.COLUMNS c
GROUP BY COLUMN_NAME
HAVING COUNT(*) > 1
ON dupes.COLUMN_NAME = c.COLUMN_NAME
Does anyone know if it's possible to fuzzy match column names or taking different prefixes into account to make this work? The very same question has been asked directly to the youtube OP. It can also be found on reddit.com, but the question remains unanswered.
I'm trying to wrap my head around some more advanced Power BI features and at the same time learn some much-needed SQL, and I thought this would be a cool place to start, so any help is much appreciated!