Forum Discussion

AndreasKvis's avatar
AndreasKvis
New Member
4 years ago
Solved

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

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

 

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    4 years ago

    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.

2 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    4 years ago

    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's avatar
    rodrigosan
    Responsive Resident
    4 years ago

    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/