Native Query to Pivot Large Volumes of Data

[stuarthokie]

Hello!  I have a challenge for which I have not seen a resolution posted.  I have seen similar issues resolved, but none like what seems to be my normal challenge.  I am green to the more complex workings of PBI and am therefore seeking help.

I would like to pivot my data.  But I have restrictions that make it so column pivoting is off of the table as an option for resolution.  If possible, I would like this to occur in the Native Query.  This seems to be the best option but please correct me if I am wrong.

DATA TABLE EXAMPLE:

The "PLC Tag" names are in the same column, this is a problem

Time Stamp	PLC Tag	Value
5/3/24 2:51	Tag1	5.00
5/3/24 2:51	Tag2	10.02
5/3/24 2:51	Tag3	15.03
5/3/24 2:52	Tag1	20.05
5/3/24 2:52	Tag2	25.07
5/3/24 2:52	Tag3	30.08
5/3/24 2:53	Tag1	35.10
5/3/24 2:53	Tag2	40.12
5/3/24 2:53	Tag3	45.13
5/3/24 2:54	Tag1	50.15
5/3/24 2:54	Tag2	55.17
5/3/24 2:54	Tag3	60.18
5/3/24 2:55	Tag1	65.20
5/3/24 2:55	Tag2	70.22
5/3/24 2:55	Tag3	75.23
5/3/24 2:56	Tag1	80.25
5/3/24 2:56	Tag2	85.27
5/3/24 2:56	Tag3	90.28
5/3/24 2:57	Tag1	95.30
5/3/24 2:57	Tag2	100.32
5/3/24 2:57	Tag3	105.33
5/3/24 2:58	Tag1	110.35
5/3/24 2:58	Tag2	115.37
5/3/24 2:58	Tag3	120.38

DESIRED OUTPUT EXAMPLE:

1: The "PLC Tag" names become the headers of different columns for each unique "PLC Tag" (NOTE: there will be MANY more than 3 in this example)
2: Each row is defined by each unique Time Stamp (yes, the various tags' data have identical time stamps and are minute data)
3: The Values are placed in the proper "PLC Tag" and "Time Stamp" intersection

Time Stamp	Tag1	Tag2	Tag3
5/3/24 2:51	5.00	10.02	15.03
5/3/24 2:52	20.05	25.07	30.08
5/3/24 2:53	35.10	40.12	45.13
5/3/24 2:54	50.15	55.17	60.18
5/3/24 2:55	65.20	70.22	75.23
5/3/24 2:56	80.25	85.27	90.28
5/3/24 2:57	95.30	100.32	105.33
5/3/24 2:58	110.35	115.37	120.38

NOTE: I believe achieving this using a Native, Folded Query is the goal due to processing concerns. Pivoting the data on the back end is a non-workable option due to tens of millions of rows of data and counting. Pivoting either crashes my machine or takes several hours to refresh if successfull. The data are so many that Incremental Refreshing will also be employed each day, multiple times per day, as the data table grows.

NOTE: Achieving this is NOT how it will end up looking in the visual; this is only step 1: desired organization of data. There will be MANY new columns of calculated metrics created that will have somewhat complicated expressions incorporating one, some, or MANY of the "PLC Tags'" values.

[lbendlin]

Your data is already in the perfect format for ingestion. Resist the urge to mess with it, and do all your pivoting in the visuals.

[stuarthokie]

Thx, Ibendlin!  The desired result shown is not at all what my visuals will look like, only what the resulting table will look like.  In fact, there are exactly zero items from either the original or the resulting table that will be a part of the visuals: no time stamps, no "Tag Names", no values.

This challenge is to get the data in a desired format in order to reduce processing on the back end so that I may add many new columns of calculated metrics created that will have somewhat complicated expressions incorporating one, some, or MANY of the "PLC Tags'" values.  It is these new calculated metrics which will be shown in the visuals.

[lbendlin]

Put your data into a matrix table, then examine the DAX query that Power BI creates for that (including filters).  Use that query as the basis for your measure calculations.

[stuarthokie]

Hello, Ibendlin!

I'm not sure what I was supposed to see but there is nothing helpful in this code from the matrix table:

EVALUATE

    TOPN(100, 'DataTable')

[lbendlin]

Use Performance Analyzer on the Matrix visual. Copy the query and examine it.

[stuarthokie]

There is no query to copy which makes sense.  All I am doing is selecting rows, columns, and values to place in the Matrix:

[lbendlin]

That's not all you are doing.  You are also applying filters.

Click Start Recording, Refresh Visuals, Copy Query.

[stuarthokie]

I did not refresh visuals previously, thanks!  I'll disect this code when I have time and get back to the thread.

[Anonymous]

Hi @stuarthokie ,

Did your problem get solved? If so, please mark lbendlin 's answer as the solution.

Best Regards,
Wenbin Zhou

[stuarthokie]

As promised, I have finallly found a solution.  It has nothing to do with what can be done in PBI.  Due to the large volumes of data, everything I tried to do to pivot the data failed in the PBI environent.  I had to resort to creating an entirely new table view in the data synapse.  Sorry this won't help anyone else out other than not wasting time trying to pivot large volumes of data in PBI.  Best case scenario, it was able to complete the pivot (albeit a couple of months ago, when there were far fewer data than now and, indeed, vastly fewer than there will be 10 years from now) in 3h ONLY ONCE.  All other times, crash and burn.

[stuarthokie]

Hello!  No, not solved, still working on it.  If I am able to come up with the solution, I will post it here for others.

[stuarthokie]

OK, thanks!  Will give it a go and get back to the thread.