Forum Discussion
Native Query to Pivot Large Volumes of Data
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.
12 Replies
- lbendlin
Super User
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.
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.
