SOLVED

Auto refresh troubles

%3CLINGO-SUB%20id%3D%22lingo-sub-1267279%22%20slang%3D%22en-US%22%3EAuto%20refresh%20troubles%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1267279%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMost%20of%20the%20data%20is%20held%20in%20an%20IBM%20iSeries%20database%20and%20the%20company%20stipulated%20the%20user%20'application'%20had%20to%20be%20in%20Excel.%20Five%20tables%20are%20the%20source%20for%20a%20load%20of%20formula%20lookups.%20The%20workbook%20is%20a%20macro%20enabled%20template%20residing%20in%20Sharepoint%20to%20allow%20a%20restricted%20group%20of%20users%20access.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBecause%20of%20the%20auto%20refresh%20options%20in%20the%20service%2C%20ideally%2C%20I%20would%20have%20used%20PowerQuery%20in%20Power%20BI%20to%20pull%2C%20shape%20and%20publish%20the%20data%20but%20unfortunately%20(to%20the%20best%20of%20my%20knowledge)%20you%20are%20limited%20to%20the%20data%20via%20pivot%20instead%20of%20an%20Excel%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20I%20have%20ended%20up%20using%20PQ%20in%20Excel%20to%20pull%2C%20shape%20and%20present%20the%20tables%20in%20an%20Excel%20workbook%20and%20for%20performance%20from%20a%20user%20perspective%20loading%20THAT%20into%20the%20final%20application%20for%20the%20lookups.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20essentially%20have%20the%20front%20end%20doing%20all%20that%20was%20required%20but%20I%20am%20left%20manually%20refreshing%20the%20queries%20daily.%20I%20have%20looked%20at%20a%20batch%20file%2Fvb%20script%20to%20auto-refresh%20but%20am%20struggling%20to%20get%20this%20working%20on%20a%20server%20where%20no%20one%20is%20logged%20on.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can't%20help%20feeling%20there%20is%20a%20better%20way%20or%20is%20my%20use%20case%20unique%20and%20struggles%20unique%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20advice%20would%20be%20greatly%20accepted.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%3C%2FP%3E%3CP%3EOli%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1267279%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1279613%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20refresh%20troubles%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1279613%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F602317%22%20target%3D%22_blank%22%3E%40DEADP00L%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOli%2C%20you%20may%20return%20tables%20as%20well.%20But%20yes%2C%20you%20have%20to%20have%20data%20model.%3C%2FP%3E%0A%3CP%3E-%20load%20data%20to%20data%20model%3B%3C%2FP%3E%0A%3CP%3E-%20at%20least%20one%20dummy%20table%20load%20to%20the%20sheet%20as%20well%3C%2FP%3E%0A%3CP%3E-%20create%20linkback%20table%20in%20the%20sheet%20(Data-%26gt%3BExisting%20Connections-%26gt%3BSelect%20any%20table-%26gt%3Bright%20click-%26gt%3BEdit%20table%20connection-%26gt%3Bedit%20DAX-%26gt%3Badd%20DAX%20expression%20which%20forma%20the%20table)%3C%2FP%3E%0A%3CP%3E-%20keep%20Excel%20file%20on%20any%20Sharepoint%20site%20or%20on%20OneDrive%20available%20for%20end%20users%3C%2FP%3E%0A%3CP%3E-%20connect%20this%20file%20to%20any%20Power%20BI%20workspace%20(not%20import%2C%20connect)%20and%20setup%20scheduled%20refresh%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

 

Hi

 

Most of the data is held in an IBM iSeries database and the company stipulated the user 'application' had to be in Excel. Five tables are the source for a load of formula lookups. The workbook is a macro enabled template residing in Sharepoint to allow a restricted group of users access.

 

Because of the auto refresh options in the service, ideally, I would have used PowerQuery in Power BI to pull, shape and publish the data but unfortunately (to the best of my knowledge) you are limited to the data via pivot instead of an Excel table.

 

So I have ended up using PQ in Excel to pull, shape and present the tables in an Excel workbook and for performance from a user perspective loading THAT into the final application for the lookups.

 

I essentially have the front end doing all that was required but I am left manually refreshing the queries daily. I have looked at a batch file/vb script to auto-refresh but am struggling to get this working on a server where no one is logged on.

 

I can't help feeling there is a better way or is my use case unique and struggles unique?

 

Any advice would be greatly accepted.

 

Cheers

Oli

 

1 Reply
Highlighted
Best Response confirmed by DEADP00L (New Contributor)
Solution

@DEADP00L 

Oli, you may return tables as well. But yes, you have to have data model.

- load data to data model;

- at least one dummy table load to the sheet as well

- create linkback table in the sheet (Data->Existing Connections->Select any table->right click->Edit table connection->edit DAX->add DAX expression which forma the table)

- keep Excel file on any Sharepoint site or on OneDrive available for end users

- connect this file to any Power BI workspace (not import, connect) and setup scheduled refresh