Data transfornation, create columns from rows

[Franz]

good evening,

i have a problem and hope you can help me: it is about masterdata, there are two tables in my model

• dim_itemtable containing general masterdata
• dim_supplierinfo containing supplier’s information

I would like to create a table with the masterdata’s and 1 column for every supplier information, avoiding multiple lines when creating a table visual,

I am sure, that it is about pivot or unpivot and seemed to be simple but unfortunately I cannot manage.

please find all details in the annexed samplefile

thanks for your help in advance, greeting from austria

Franz

suppfile_180720.pbix (53.7 KB)

[Melissa]

Hi @franz,

Here are the steps I took.
First, with denomination selected used Group By with this result

From that table extracted the data in the supplier info column

Split the supplier column by delimiter

Last step, is to Merge with the item table

Here’s my sample file. eDNA - data transformation.pbix (50.2 KB)
I hope this is helpful.

[Yeriel]

Hi @Franz!

I hope the detailed answer post by @Melissa was able to help you.

I found a video post by Curbal that describes the different ways you can Join or Merged your data.

Explain the different types of join/merge available in Power BI/ Excel

Hope this info can help you with future inquiries.

Cheers!

Yeriel.

[sprmnt21]

probably with a clever use of the pivot unpivot functions you could do better and earlier.
but waiting for a better idea to come to mind or someone else to post it, here’s how it could be done …
columns from rows.pbix (65.7 KB)

[Franz]

thank you very much, i could implement this successfully

[Franz]

very smart use of “grouping”
thank you very much, i could implement this successfully