How to flatten Smartsheet with hierarchy levels into Table file for PowerBi
I have a Smartsheet with hierarchy levels in it. I need to pull this file into PowerBi for reporting. My understanding is that PowerBi needs a flat table file. I've done this successfully with other sheets but it's not pulling over the data correctly in a Sheet with parent/child relationships.
I've seen the post were helper columns were added to identify if the row is a Parent or a Child. I can do that easy enough. I'm confused as to what the next step would be to create a Table file for PowerBi?
Any ideas would be helpful!
Jeana
Best Answer
-
Hi @Jeana
That's interesting; I haven't seen it where the import filters out rows. It should bring in the whole sheet but remove out the hierarchy, which is where a "parent" indicator would be helpful, like in this other Community post or this other post, here.
Once you have screen captures, you may also want to try posting in the Power Bi Community in case other Power Bi users have come across a similar behaviour.
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Answers
-
Hi @Jeana
Do you want the Power Bi table to include the Parent levels, or just to have an identifier that they are Parent rows?
If you can create the helper column which identifies the hierarchy, then you could use a Report to only gather the rows that are a certain level (ex. only the Child rows, or only the Parent rows). Then you can use the Report as the source for your PowerBi report. Would this work for you?
See Microsoft's documentation, here: Connect to Smartsheet with Power BI
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Hi Genevieve,
Good question. Here's a little more background. PowerBi is still sort of new to me so I have tried 'getting' the file two ways. When I link directly to Smartsheet (with the hierarchies) I don't get data that is associated with the Parent rows. When I export to Excel and make it a Table file I can see the hierarchy but again PowerBi doesn't show the data in the Children files.
It could be that I'm going about this all wrong since it's new to me. Unfortunately my organization doesn't have much expertise in connecting PowerBi and Smartsheet.
Let me run the process and grab some screen shots for a better understanding of what I"m seeing.
Thanks for your patience!
Jeana
-
Hi @Jeana
That's interesting; I haven't seen it where the import filters out rows. It should bring in the whole sheet but remove out the hierarchy, which is where a "parent" indicator would be helpful, like in this other Community post or this other post, here.
Once you have screen captures, you may also want to try posting in the Power Bi Community in case other Power Bi users have come across a similar behaviour.
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 304 Events
- 34 Webinars
- 7.3K Forum Archives