"Hidden Columns" in Source Data

[Anonymous]

I am pulling in data via a SQL Analysis Services database.  When accessing the data in Excel through a data connect there is expandable data.  The information I am pulling agregates to a sales order and again in Excel each record has a button within the cell and if I click one then it expends the entire column to display additional data.  The expanded includes all the line item detail in the sales order plus some additional data fields.  When I bring the data in via Power BI I do not see an apparent way to expand this data.  Furthermore, when I am choosing which data fields to import, it is just the sales order as an option and no mention or evidence of the line item and other "hidden" data.  I am assuming that any means to expand this data would be found at the point of data query?  I'm importing roughly 50k records, if I was to expand to the sales order line items then that data would liekly increase by about 5x to 10x.  Although again, I am not thinking there is not a means to expand by data after it has been loaded.  I am not an IT person but a finance person so I apologize if I am not explaining myself correctly.  However I would be happy to provide more details if anyone if picking up on the clues that I am sprinkling out here.  Thanks!

[v-yuezhe-msft · Accepted solution]

@Anonymous,

Do you use Data->From Other sources->From Analysis Services option in Excel to connect to your database and get the the data as shown in image2?
image1image2
If so, when connecting to SSAS database in Power BI Desktop, please choose "Connect live" mode. This way, you will get similar data structure as that in Excel. You can review image4 to get more details.
image3image4

However, if you use import mode in Power BI Desktop to connect to the database, the method is same as that you use "Data->New Query->From database->From SQL Server Analysis Services Database" option in Excel.


Regards,
Lydia 

[v-yuezhe-msft · Accepted solution]

@Anonymous,

Do you use Data->From Other sources->From Analysis Services option in Excel to connect to your database and get the the data as shown in image2?
image1image2
If so, when connecting to SSAS database in Power BI Desktop, please choose "Connect live" mode. This way, you will get similar data structure as that in Excel. You can review image4 to get more details.
image3image4

However, if you use import mode in Power BI Desktop to connect to the database, the method is same as that you use "Data->New Query->From database->From SQL Server Analysis Services Database" option in Excel.


Regards,
Lydia 

[Anonymous]

Lydia:  Thanks for the suggestion.  My initial import was an import.  I did try the live connection to see if it worked, but I am hoping to connect an additional data source.  As you can see for my sales order field I did not get the same "stop light" icon that you have for your data.  Any other suggestions you might have would be appreciated though. 

[v-yuezhe-msft · Accepted solution]

@Anonymous,

In connect live mode, it is not possible to connect to additional data source. We are only able to connect to a single SSAS database.

In order to get "stop light" icon  in Power BI Desktop, you would need to create measures on SSAS side.

Regards,
Lydia

[Anonymous]

@v-yuezhe-msft  Thanks for the further info.  I under the challenge of a single live connection but that's exactly what I'm trying to do in joining in more info.  I may need to go back to my IT organization.