I am hoping that the forum might be able to help guide me here. I will post several questions to the Data Setup category in the coming weeks as I believe this is such a critical part of the PowerBI experience for developers and analysts. One of the biggest challenges I have faced is trying to get my data “clean” and into a useable format prior to entry into BI. Most of the data I use is not system generated and is rather manual downloads from various sources.
Obviously it is very hard to articulate some of this stuff so I wanted to set out an example. I have attached a file. It is the a selection of market data from Bloomberg (short excel as size was too big). This excel has a tab of each security and I was about to put this into BI. I am wondering how people would manage this file in BI and in the Power Query.
a) merging into one tab
Several of the securities have the same number and column headers so I was wondering if I should add an unique identifier and merge these queries into one securities table. I can see a big benefit in terms of scaling the Power Query Transformation if it is all in one table but also several potential issues and a lot of data manipulation
b) editing in excel or in power query
I have the option to do adjust the raw set up in excel if I need but is this useful given it is pulling directly from Bloomberg and I want it to be dynamic or should I just do all of the transformation in BI?
I would very much welcome some views on to how other members might get this data into BI before I set off on the painful part of formatting data.
I applaud you working to plot this out in advance of building out your project.
Just as with Excel, there are multiple ways to accomplish things in PowerBi (I would actually argue there are more options in Power Bi when you look at DAX versus M code solutions).
And, often there is not a ‘this is the one true method’ answer. Generally the answer is ‘it depends…’
However, on to your questions (and keep in mind, this is based on my personal experience):
A - merging to one tab
Think of how you want to deal with this data after you done with it in Power Query -
Are you going to want to have a single sum of all of the tables?
If the above is true, just how many measures will need to include all tables?
Do you want to use a filter to determine which of the securities you are tracking on the page?
Any of the above items will be easier to handle if you are dealing with a single large table. Otherwise, you will have to create measures for each Security and then combine those measures. And for the filter point, you’ll need to build a table that combines the Securities into one to use as the filter.
B - editing in Excel or in Power Query
Again, as you have already mentioned, both are possible. However, I would generally suggest handling the transformations in Power Query.
You already have one manual aspect of this report which is going to get old over time - the manual download of the data. Having done this myself in my early report development, this gets old fast. So if I’m going to have any manual downloads, I ALWAYS push the data transformation to Power Query (so the manual process is only download data, rename file, and save to proper folder)
A few tips that might help with this:
If possible, set up a gateway (either personal or with the assistance of IT) - for this, I opted to go with convincing IT to set up a gateway to ‘future proof’ my solution. This way the company controls the access, and if I move to a different position, there’s less of a mess to untangle regarding ownership of the report backend data.
Even if you are not able to set up a gateway, I strongly suggest that you consider data storage - moving it to a solution like OneDrive or Sharepoint is going to make life much easier for you long-term.
If you have a gateway set up, you can actually push a lot of your data transformation steps to the gateway, and then simply build the dataset from those dataflows.
I’m sure that others will chime in with their own suggestions, and you’ll probably still have some trial and error as you get things set up - so, as we learned with the events of last year, be prepared to change some portion of your plans.
Hi @Ronan, did the response provided by @Heather help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!
Well I got the data in alright but the question was more open to get some various suggestions so that I can continue to have the data in the best format possible in various reports. So I will mark it as solved but if you had any further thoughts to add that would be great.
A response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!