How to transform columns to rows in a copied table under direct query

[JensL]

Is it possible in Power BI to transform columns to rows in mode direct query keeping the current table layout and in the copied table the transform function? Or can this also be solved in another way?

[v-jianpeng-msft]

Hi, @JensL 

In Power BI, the converting column is usually implemented by pivot column function.

 

However, the data model in the direct query mode is different from the import model. The data model in the direct query mode does not completely load the data to Power BI, but directly query the data through the data source. Therefore, the data conversion capacity in directly query mode is limited.
To convert columns to rows and retain the current table layout, the import data model of Power BI is usually required. Once the data is imported, you can perform reverse perspective operations in the Power Query editor and convert columns into lines. Then you can create a new table on the Power Bi desktop, then add the conversion data to this new table, while retaining the layout of the original table.

If you have to work in a direct query mode and need to convert columns to line, this may be more challenging. In this case, you may need to execute the required conversion in the data source (such as SQL Server, Oracle, etc.), and ensure that the data is already conversion format when loading to Power BI. This usually involves using Unpivot operations in the database or conversion during ETL process. If you want to achieve this function in Power BI, you may need to use DAX to create a calculation table or calculation list, which can simulate the effect of passing the columns to a certain extent. But this depends on the specific data structure and business needs, and may require complex DAX expressions. Generally speaking, the column is converted into lines and retaining the current table layout may not be a simple task in direct query mode. It may need to be converted in the data source, or use DAX in Power Bi to perform more complicated processing. If you provide more specific data structure and needs, I can provide a more detailed solution.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[_AAndrade]

If you use import mode you could tmdo this kind of transformation in power query