Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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:
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:
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!
Sounds like a lot of hard coding. Have you considered unpivoting your meta tables instead and then applying the same transform to all rows?