Expand multiple tables with different, varied headers

[Anonymous]

I have an iterative API call in Column 4 (variables in columns 1-3). Then, I invoke the API function and get a table returned for each line. The tables contain different data (different column names, column numbers) than each other. As you can imagine, expanding these tables the typical BI way isn't effective, as I end up with rows of values and a row of headers that aren't consistent. What I'd like to do is (prior to expanding each table) pivot the header row so each table looks like {Attribute, Value}. Then when expanding, the data is consistent through the two remaining columns.

How do I do this? I can't find a way to access each table before expansion unless there's a way to store and append tables within variables in M? Is there something else I'm missing? Below is a sample of the table data post expansion (actual file is sensitive)

Attr1	Attr2	Attr3	APIcol1	APIcol2	APIcol3	APIcol4	APIcol5
asdf	adf	adf	Date	TableApple	TableOrange	TableBanana	TablePineapple
asdf	adf	adf	Oct 5	5	7	8	9
asdf	adf	adf	Oct 12	3	1	8	10
asdf	as	as	Date	TableTruck	TableCar
asdf	as	as	Oct 5	1	0
asdf	as	as	Oct 12	2	2

[Fowmy]

@Anonymous 

If you have the possibility to create a dummy file with sample data and share the expected result then, it will give a handle to work out a solution.

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn 

[Anonymous]

@Fowmy Below is a link to my sample file. Unfortunately, I do not know how to make a column of different tables, but the file reflects how the data looks upon expansion. Note that I have several hundred of these tables so an automated solution is necessary.

https://1drv.ms/u/s!AuFhcdA3iSEvgdNTOZopROFpPB4pAw?e=3Lgue7

My expected output is ideally as follows (based on the file, numbers differ from example above slightly), but suggestions are welcome.

APIParam1	APIParam2	Date	UnpivotedAttribute	UnpivotedVal
thing1	thing2	Oct 5	TableApple	2
thing1	thing2	Oct 12	TableApple	9
thing1	thing2	Oct 5	TableOrange	25
thing1	thing2	Oct 12	TableOrange	8
thing1	thing2	Oct 5	TableBanana	5
thing1	thing2	Oct 12	TableBanana	7
thing1	thing2	Oct 5	TablePineapple	2
thing1	thing2	Oct 12	TablePineapple	1
thing1	thing2	Oct 5	TableTruck	75
thing1	thing2	Oct 12	TableTruck	4
thing1	thing2	Oct 5	TableCar	6
thing1	thing2	Oct 12	TableCar	4