Forum Discussion

nirvana_moksh's avatar
nirvana_moksh
Icon for Impactful Individual rankImpactful Individual
7 years ago
Solved

Transpose of table

Hello All,   So I have tried pivot and having an index column in the main table and few other approaches but I cannot achieve the following. My main table is like below:   ID Name Name 2 Da...
Need Help
Show and Tell
Tips and Tricks
  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    Hi,

    This M code works

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Name", type text}, {"Name 2", type text}, {"Date", type text}, {"Detail", type text}, {"Rank 1 ", Int64.Type}, {"Rank 2", Int64.Type}, {"Rank 3", Int64.Type}, {"Category", type text}, {"Movement", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
    Partition = Table.Group(#"Unpivoted Other Columns", {"Index"}, {{"Partition", each Table.AddIndexColumn(_, "Index1",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Attribute", "Value", "Index1"}, {"Attribute", "Value", "Index1"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Partition", {{"Index", type text}}, "en-IN"), List.Distinct(Table.TransformColumnTypes(#"Expanded Partition", {{"Index", type text}}, "en-IN")[Index]), "Index", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index1"})
in
    #"Removed Columns"

    The only thing i cannot get to work here is the order of the items in the attribute column to match your order.

    Hope this helps.

parry2k's avatar
parry2k
Icon for Super User rankSuper User
7 years ago

nirvana_moksh using index column make sense to bring unique id. So what exactly is not working on your dataset?

nirvana_moksh's avatar
nirvana_moksh
Icon for Impactful Individual rankImpactful Individual
7 years ago

parry2k  - Using the index column for the relationship for some reason does not filter the secondary table correctly (clicking on a row on the main table shows up the "first value" of some other row). Just for the sake of it, I used the ID column and that worked, which I think is odd. In your opinion is the usage of an

parry2k wrote:

nirvana_mokshusing index column make sense to bring unique id. So what exactly is not working on your dataset?


index column as a unique identifier a good approach?  Because I do use the same logic for other solutions for dynamic axis change. When I check the index column id for one row on the main table and the same for the second it matches, but the value output is not correct for some reason

 

Thank You

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    7 years ago

    nirvana_moksh i used index it worked as expected. Not sure why in your case it wouldn't work. Does it not work for any of the header level record or works for few and not for others. Just trying to narrow down the issue here although there is no logical explanation.

    • nirvana_moksh's avatar
      nirvana_moksh
      Icon for Impactful Individual rankImpactful Individual
      7 years ago

       parry2k - Sadly, doesn't work for anything at all. Like for row 1 see the index is 1 and after that when I see what is the output from the second table and the index for 1 shows up a value for say index 2987. Still trying to figure what can wrong in this process.