Matrix with different datatypes (picture, number, text)

[Anonymous]

We have a simple product list on SPO (Sharepoint Online) with this cols:

- titel

- picture (URL)

- property a (text)

- property b (number)

- property c (date)

...

- property z (Yes/No)

Now i want to show the selected products in a product-matrix!

I unpivot all columns except 'title'

If i change the data-cathegory for the resulting 'Values"-column to 'Image URL' i can see the product picture in the matrix but not the other properties and vice versa.

Is there away to preserve the format/data category of the columns after unpivoting?

Thanks for helping me.

Johannes

[v-lili6-msft · Accepted solution]

Hi@ gaj

After my research, you can use sort by function and do these follow my steps like below:

Step 1:

Rename column in Edit Queries

Format: ’decimal number’+’ ’+’title’

I recommend use ‘ ’ as delimiter

For example

Be care for that there is one ‘ ’ between ’decimal number’ and ’title’

Step 2:

Unpivot columns

Step 3:

Select column Attribute and then click  Add Column->Extract->Text Before Delimiter

Entry “ ” click OK

Step 4:

Change the type of column Text Before Delimiter to Decimal Number and then Click Close&Apply

Step 5:

Drag fileds into table then select filed Attribute click Modeling->Sort ->Sort by Column -> Text Before Delimiter

Result:

Best Regards,

Lin

[Anonymous]

Hi Lin,

Now i have a nother problem with the Images. If I publish the report to a ppublic URL everybody with the URL can see the images.

If I use the report in MS Teams or in SPO in a modern site only the Admin can see the images, also the same in Power BI Webclient and Power BI Windows Client.

I have stored the images in an SPO picture library (because i want to use the rendition-options).

Can you help me?

thx.

Johannes

[Anonymous]

Sorry,

If I integrate Power BI in SPO on a modern site the images are shown, but not in MS Teams!

[Anonymous]

Hi Lin,

Thank you, works fine

[Anonymous]

My workaround:
unpivot all cols except picture-URL-col (picture is in first row/header)!

 

Is there a way to sort the pivoted cols ? I tryed renaming (1. foo, 2.1 foo.. but after 9 problem!) the cols befor unpivoting.

thx.

Johannes

[v-lili6-msft]

Hi@ marksappleton

After my research , I'm afraid it couldn't achieve in Power BI for now , Maybe you can try to unpivot all columns except 'title' and  'picture'

and then set the data-cathegory of the column ”picture”  to 'Image URL' individual.

Then drag fileds into matrix.

Result:

Reference:https://blog.gbrueckl.at/2018/01/storing-images-powerbi-analysis-services-data-models/

Best Regards,

Lin