Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
TomJefferies
New Member

Dynamically expanding tables within tables for each column

Hello,

Im trying to write a set of queries that will help me consolidate a lot of data from many reports - there are 3 phases to look at and the phases have differing amount of data points i need to look at:
Phase 1 has 4 data tags, Phases 2 and 3 have 2 tags each.

For each tag each file has roughly 48K lines so processing the data into the format required has required a couple of longwinded steps but im happy with that step. The issue im having is as follows:
At the end of each individual file being processed the output looks something like this:TomJefferies_0-1695907695906.png

And then once all files have been consolidated for one phase the table layout now looks like this (note im using a subset of four files to develop the solution hence com1-4) but as more files are added i would see an additional column per:

TomJefferies_1-1695907910323.png

So for one finalised table i would need to expand out a specific column from the first screenshot, for example (FIT_Y01...) into each ComX column, and then from that expand the "Value" column to show the actual numerical values from the raw files.

 

The function i am writting for this is a nested table expand but i figure out the syntax i need to get the output im after.
My current function is this:

= (Tbl as table, tagFilter as text) as table => let
        cols = Table.ColumnNames(Tbl),
        TransformDef = List.Transform(cols, each {_,Table.ExpandTableColumn(Table.ExpandTableColumn(Tbl,_,{"Value"},_), _,{tagFilter}),{_}}),
        TransformCols = Table.TransformColumns(Tbl, TransformDef)
    in
        TransformCols

I would also like to keep the original column headers from the second table i showed as the column headers at the end of the function.
If it helps the M Code to expand one column looks like this on the first expansion:

= Table.ExpandTableColumn(Source, "COM1", {"FIT_Y01_03.PV"}, {"COM1"})

and then the second expansion looks like this:

= Table.ExpandTableColumn(#"Expanded COM1", "COM1", {"Value"}, {"COM1"})



Sorry for the long post ive been stuck on this for a few days and my brain is fried.

Thanks for any input and if i missed anything let me know!



1 REPLY 1
lbendlin
Super User
Super User

Sounds like a lot of hard coding.  Have you considered unpivoting your meta tables instead and then applying the same transform to all rows?

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors