Combining files from Smartsheet while maintain Hierarchy levels

Power Query
1

Hello, I am trying to combine multiple Tables from Smartsheet (a Fancy excel tool online). The target Tables hold balance sheet information, therefore maintaining hierarchy is a must.

My challenge is around maintaining the hierarchy levels of the files after combining them to display in a Matrix table. Not sure about the best way to achieve that in Power Query.

Apologies but no PBIX file to share, but I can share sample data & mapping.

1.when I connect to the data:
image

  1. Sample data with hierarcy
    image

  2. hierarchy mapping
    image

Sample data
Sample.xlsx (14.8 KB)
.
Many thanks

3

Hi @Hesham ,

Is the supplied sample (2) an accurate representation of all the nested tables (1)?

Can you supply the expected result/format you’re looking for.

Thanks

4

Hi @Melissa ,

Many thanks for looking into my question. I have updated the sample file to give you a more accurate representation of the nested tables.

Sample.zip (31.7 KB)

Expected result sample
Expected Outcome.xlsx (14.4 KB)

Please note that I am not connecting to a folder to combine files, instead I get the below view from Smartsheet:
image

Thanks again, Hesham

5

I’ve had a look but the supplied sample doesn’t seem to match the expected outcome…
Can you clarify how the data should be aggregated, thanks.

6

I think I managed to get the financial layout the way I want by adding a helper layout table. Also made a minor tweak to the names to show employer1 emp2 emp3.

So now my question is about merging columns. Do you have any recommendations for dynamically merging columns that have month names like the below:

image

The aim is to replace the column with the latest data (i.e. March data will replace Feb data in the column). Not sure if it’s possible or not.

Here is test file.pbix (68.7 KB)
file.

7

Hi @Melissa, sorry to ask, but did you get a chance to look into my query, please?

8

Hi @Melissa , please ignore my query. I have sorted my issues out. Happy days!

Thanks

9

Hi @Hesham,

Glad to read you’ve resolved this.
:+1: