0

I have the following table in Excel 2016:

ID  Type1            Type2            Type3  
1   01.03.2018       null             01.02.2019
2   01.04.2018       01.05.2018       null
3   null             01.03.2018       01.05.2018

Now I am trying to index those values into a new table which should look like the following:

ID  01.02.2018       01.03.2018       01.04.2018    01.05.2018     
1   Type3            Type1            null          null
2   null             null             Type1         Type2
3   null             Type2            null          Type3

What is the best way to achieve that? Using Power Query or with Excel functions? And how can I achieve that transformation?

Improve this question
4
  • What does you want to obtain when 2 different TypeN have the same date for different ID?
    – Akina
    Commented Sep 18, 2019 at 13:15
  • @Akina Duplication is possible. I just want to change the view, group the types by the dates by transforming the date to columns.
    – Mango D
    Commented Sep 18, 2019 at 13:18
  • Duplication is possible. And? does you want to obtain something like Type5,Type8 in a cell with according row (ID value) and column (date value)?
    – Akina
    Commented Sep 18, 2019 at 13:22
  • I want to achieve exactly what you see in table 2. For each ID there should be visible which types are occuring in which date. I want to transform table 1 to table 2.
    – Mango D
    Commented Sep 18, 2019 at 13:31

2 Answers 2

Reset to default
0

You can do this all in Power Query

  • Select the ID column and Unpivot other columns
  • Optionally Sort the rows in the Value column
  • Pivot on the Value column, with the Values column set to Attribute and the Advanced options select Don't Aggregate

enter image description here

That's it!

M-Code

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Type1", type date}, {"Type2", type date}, {"Type3", type date}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID"}, "Attribute", "Value"),
    #"Sorted Rows" = Table.Sort(#"Unpivoted Other Columns",{{"Value", Order.Ascending}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Sorted Rows", {{"Value", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Sorted Rows", {{"Value", type text}}, "en-US")[Value]), "Value", "Attribute")
in
    #"Pivoted Column"

Source Data

enter image description here

Results

enter image description here

Null is a reserved word in M, so if you need to show the actual word "null", you need to replace null with something that makes it a string. You could use something like #(00A0)null which prefixes the null with the NBSP, or #(200B)null which is ZWSP (zero-width space). And there may be other tricks you can use.

Improve this answer
0

I would tackle this requirement with a mix of Power Query and Power Pivot (Excel Data Model).

First I would build a Power Query. Select the ID column and use the Unpivot Other Columns feature to transform the data into Attribute (input column name e.g. Type1) and Value (input cell value e.g. 01.02.2018). I would set the query to load into the Data Model (Power Pivot).

Then I would create a Pivot Table based on the Data Model, using the Value column for the Rows and the Attribute column for the cells.

You can rename those generated columns to suit. I prefer to do this in Power Query for better transparency.

Improve this answer
2
  • Sound good! I did the step with Power Query and opened the data model in Power Pivot but never used Power Pivot before. How can I set the Value column for the rows and Attribute column for cells?
    – Mango D
    Commented Sep 18, 2019 at 15:59
  • Mmmm Mangos ... From the Insert ribbon choose Pivot Table, then for "Choose the data ..." select "Use this workbook's Data Model".
    – Mike Honey
    Commented Sep 19, 2019 at 3:17

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .