Financial Market Data_for EDNA Forum.xlsx (3.7 MB) Hello all,
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.