Forum Discussion
Converting columns to rows on Power Bi service
Hi All,
How can I convert from Columns to Rows on Power Bi Service? As I am using DirectLake mode and creating report on server, the option of Pivot data is not coming. M Code, Advanced Query Editor and Unpivot options are not available because we are using Direct Lake mode and creating report on server
Also tried DAX but DAX is not allowing to create table. It's allowing to create only measures.
Please guide on how to convert from Rows to Columns on Power Bi service using Direct Lake mode.
Thanks.
4 Replies
- lbendlin
Super User
Let the Matrix visual do the work for you.
Thanks for your reply, but I need to prepare line graph based on the data achieved after converting from columns to rows.
- lbendlin
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
Hi,Harshi10.Hello, lbendlin ,thanks for your concern about this issue.
And I would like to share some additional advice below.
I am glad to help you.According to your description, you want to perform pivot operations on the data on the service (perform conversion between rows and columns). But it is not possible due to the specificity of the data source (Driect Lake)
DirectLake has the following features:
Direct Lake is a highly flattened data storage method, usually used to store raw, unprocessed, large-scale data, and therefore does not support pivot operations such as row-column conversion.
Direct Lake provides a delayed loading feature that allows data to be loaded dynamically at query time, rather than loading the data into memory ahead of time. This delayed loading feature may affect the use of the Calculated Columns and Calculated Tables features in Power BI. (Because calculated columns need to be calculated for the entire dataset, and DirectLaked has high latency.)
Below I am suggesting possible solutions for you to consider:
1.Use Azure Data Lake Analytics
Azure Data Lake Analytics is an analytics service that can be used to perform complex data transformation and analysis operations on large-scale datasets. You can write scripts to implement pivot (row and column transformation) operations.
Here is the official website for Data Lake Analytic
url: Data Lake Analytics | Microsoft Azure2. Copy the data to another data store that supports pivot
Such as Azure SQL Database.I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
