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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
JensL
New Member

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

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?

2 REPLIES 2
v-jianpeng-msft
Community Support
Community Support

Hi, @JensL 

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

 vjianpengmsft_0-1708409153341.png

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
Super User
Super User

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





Did I answer your question? Mark my post as a solution! Kudos are welcome.

Proud to be a Super User!




Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.