I am trying to use Power BI for the first time (I'm new to Power BI) to report on data which is too big to use easily in Excel, and am having a great deal of difficulty getting my data into PowerBI in the first place. Please help.
At our company we have an SQL database of transaction records covering more than 10 years of history, with several million records. When I try to access this SQL server data source from PowerBI, I see this database has over 300 data tables. These tables are named such that I can easily identify which data columns are used to reference other tables (basically, multiple look-up tables), but PowerBI does not seem to understand those data table relationships automatically, and when I use 'manage relationships' none of the data table _ID columns are listed as being available to establish relationships between tables. When I use the Query editor, every _ID column is listed as having a 'binary' data type and is displayed in a different color from all other data types which I can see in 'manage relationships'.
Now, I am an end-user not a database administrator but I can use Microsoft SQL Server Management Studio 17 to see the database structure, and I can see that all of these _ID columns are data type "binary(8)", which is just another way to say its a 64-bit integer number (LINT). So in Power BI's query editor I try to manually tell PowerBI that the _ID Colum’s data type is 'integer', but it does not work, with PowerBI simply reporting 'error' in the data Colum, instead of 'integer'.
So, what do I need to do in PowerBI to work around this issue? I've ready that 'binary' data types are not supported in PowerBI, but that is not acceptable to me as these are simply 64-bit reference numbers for the lookup tables, not something complex like pictures or other binary data. So how can I force PowerBI to recognize that these ID columns are simply 64-bit numbers?
