How to flatten Smartsheet with hierarchy levels into Table file for PowerBi

Jeana

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

Genevieve P.

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

Genevieve P.

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

Jeana

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

Genevieve P.

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