Get labels for choice cols + Convert Cols to Rows for table visual.

[VisualJade]

I have 2 questions: 

1. How to get labels for choice columns from dataverse/power apps? 
Then I need to use these choice columns for below no2. 

2. How to convert columns to rows, for 1000+ rows. 
This is what my table looks like.
My table is has 1000+ rows and counting...

This is what I would like to get in the matrix/table visual. The data will be filtered by a "Name" and "Country" slicer. 

I only know to not use transpose or pivot, cuz 1000+ rows. 

Anyone knows how to solve this? Thank you

[VisualJade · Accepted solution]

Solved this on my own. I have no access to Azure Data Lake, so I made do. 
1. Download sample powerbi table from power apps. Copy Paste to PQ code. 
2. In new PBIX file, paste PQ code for the first table. Create separate tables for each column: A1 - A10 gets TableA; B1 - B10 gets TableB.
3. Unpivot columns of all tables. The connection will still be in DirectQuery. 
4. Might want to create a key column for all the tables, ie merge [ID no] & [Col No.] - Col No. being 1 - 10. That way, can connect TableA to TableB via Merged column.

[v-yilong-msft]

Hi @VisualJade ,

For the first question I think you can utilize Azure Synapse Link for Dataverse. this feature supports continuous replication of data and metadata (including option tags) from Dataverse to Azure Data Lake Storage (ADLS), which can then be queried using Azure Synapse Analytics. I hope this document helps you: Access Dataverse choice labels directly from Azure Synapse Link for Dataverse - Power Apps | Microso...

For the second question, I don't think right now Power BI Desktop has this intelligent way to handle the functionality about converting between thousands of rows and columns.

Best Regards

Yilong Zhou

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