locked
Controlling M Code in Various spreadsheet RRS feed

  • Question

  • We track our projects on excel spreadsheets. In the excel file, I have created a power query to update weekly hours booked to the project from a CSV file. A new CSV file is generated each week and dropped in various location (fileserver/ sharepoint/ ondrive). 

    The query deals with manual entries and does a recon if any of the historical hours needs to change - it uses Excel.CurrentWorkbook. 

    I will run a couple of dozen of these spreadsheets. I plan to use them as a enterprise data source for power BI. 

    How can I manage the code in each excel file? Is it possible to write a function in a Power BI data connector that Excel can call?

    Is there a way to control the M Code centrally? Would I have to write VBA code to update Power query code?

    Any suggestions?


    Thursday, May 9, 2019 2:20 PM

Answers

  • Hi there. Why not just do the PQ work in Power BI instead of Excel, and centralize it?

    Ehren

    Tuesday, May 14, 2019 6:44 PM
  • Also like Ehren said, I'd suggest to separate the data entry part from the data transformation or analysis.

    Let user enter their data whereever it suits them, but collect this data in a central place before eventually sending it out or providing it from a central place to your users. If you don't want to go Power BI, you still can do it in an Excel-workbook. But it should be only one and not multiple.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Sunday, May 26, 2019 5:07 AM

All replies

  • Hi there. Why not just do the PQ work in Power BI instead of Excel, and centralize it?

    Ehren

    Tuesday, May 14, 2019 6:44 PM
  • Also like Ehren said, I'd suggest to separate the data entry part from the data transformation or analysis.

    Let user enter their data whereever it suits them, but collect this data in a central place before eventually sending it out or providing it from a central place to your users. If you don't want to go Power BI, you still can do it in an Excel-workbook. But it should be only one and not multiple.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Sunday, May 26, 2019 5:07 AM