Transform data: Transpose, Pivot, Unpivot or another idea?

[AndreasKvis]

Hi all,

I have the following SAP data:

As you can see, the 4 rows of data is very similar, except for the partner column. Therefore, it would like to transform and group the data into a solution similar to the following: 

Do you guys know wether this can be acheived through the Power Query?

Note: The data is only dummy data, and in the real dataset, i have mulitple notifactions with different numbers of partners (in the example above there is 4 partners, however, it can vary from 3-6 parterns.

In advance - thank you for your help

/Andreas

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Notification", Int64.Type}, {"Typ", type text}, {"Create On", type date}, {"Material", type text}, {"Partner", type text}}),
    Partition = Table.Group(#"Changed Type", {"Notification"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Typ", "Create On", "Material", "Partner", "Index"}, {"Typ", "Create On", "Material", "Partner", "Index"}),
    #"Added Prefix" = Table.TransformColumns(#"Expanded Partition", {{"Index", each "Partner " & Text.From(_, "en-IN"), type text}}),
    #"Pivoted Column" = Table.Pivot(#"Added Prefix", List.Distinct(#"Added Prefix"[Index]), "Index", "Partner")
in
    #"Pivoted Column"

Hope this helps.

[rodrigosan]

Hello, for your case, wouldn't it be better to divide it into a fact and dimension table?
Below is the deal I made:

Based on the data I created two tables

Then I created the relationship:

I understand that this way your analyzes will be much easier.

If you want to download the example file I created, follow the link:
https://github.com/roolsant/SOSPowerBI/blob/main/Transform_Data.zip

I hope I helped you.

Rodrigo Santos
https://www.linkedin.com/in/rodrigosanpbi/