How to avoid binary file usage

Anonymous · ‎03-05-2021

Hello,

I need to prepare dashboard based on existing report in excel. The issue is that the extension of 1) excel file is binary and I want to refresh it online & 2) person responsible for this report is sometimes changing columns orders, names etc so it will not be the same dataset structure every time,

2) I've been also provided with Access DB , which is the source for Excel report but the Access DB is on someone personal One Drive, so I don't want to use it in case someone will leave the company.

I was thinking about copy Access DB to SP weekly by Power Automate so I will not take data from someone's onedrive but what are the options to avoid connecting to Access DB so that everything can be done automatically?

Any ideas?

ImkeF · ‎03-07-2021

Hi @Anonymous ,
not sure what the problem is with the binary?
Usually, Power Query is not interested in the order of the column names. Your reference a column by its name and thats it. What can become a problem is when your columns don't have names and you have to reference them by their position. In that case, you're usually working with automatically created column names like "Column1", "Column2" and so on.
So please provide more details what the actualy problem is please.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries