Widen a Table

[Anonymous]

If I have a table like this:

How can I widen it into something like this:

Thank you for your help.

[PANDAmonium · Accepted solution]

I actually ran into a similar problem yesterday. So first make sure this is the way you want to model it vs splitting up employees and tests. If this is what you actually need, here are the steps...you have two options:

Option A (easy but requires multiple tables)

1. Duplicate your table for each of your tests.
2. On original table, remove duplicate rows on Employee
3. On original table, remove cols other than employee and job
4. On one of the new tables filter test to Test A.
5. Repeat for other two tables filtering on Test B and Test C
6. Merge Test A into your original table on employee = employee.
7. Expand Status and Dates column leaving the prefix of the table name (Test A.) or something similar.
8. Repeat for Test B and C

Option B (more difficult but able to do with just one table)

1. Duplicate your test coluimn twice. So you will end up with 3 Test rows.
2. Highlight one of the Test column and the Status columns then select select Pivot Columns w Status as the value and under advanced options select Don't Aggregate.
3. This will create additional cols for Test A, Test B, Test C. So rename each to Test A Status, Test B Status, Test C Status.
4. Repeat steps 2 and 3 for Date1 and Date2
5. Group Rows with a group by of Employee and Job and aggregations for each of your new columns with an operation of Max.

The downside with both these solutions is if there is ever a Test D you will have to write that into the query.

[MFelix · Accepted solution]

Hi @Anonymous ,

Although the @PANDAmonium  is a good solution this is a very complex way of making this work.

Based on the solution given on this blog post there is a much easier way of making this work:

• Select the columns Status, Date1 and Date2
• Unpivot Columns
• Create a new column with the following syntax:

[Test] & "," &[Attribute]

• Remove columns Test and Attribute
• Pivot Column that was created in previous step.

Check the full code below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hc/BDsIgDAbgVzE98xKOeJgHL9tt4YBYFxQ7w6aJb7+CnTGGxQNQkq/0p+tgb90VFDQ24Mhni+O02XKheV0spf1BYFRZVlwcGkEK7D2uUr3QhBQMbnpTHwJfd9R7Ioye+t8UNDzzgPDHV+XUq/4T6GajtOUGpNRft9855I9B4hTdMv+ETh4tMi3sjMfMXmDMDA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Employee = _t, Job = _t, Test = _t, Status = _t, date1 = _t, Date2 = _t]),
    #"Unpivoted Columns1" = Table.UnpivotOtherColumns(Source, {"Employee", "Job", "Test"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns1", "Test_Column_Name", each [Test] & "," &[Attribute]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Test", "Attribute"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Test_Column_Name]), "Test_Column_Name", "Value")
in
    #"Pivoted Column"

Regards,

MFelix

[PANDAmonium]

Oh, cool. I had to try this for myself; I was hoping someone had a better solution.

Yeah, go with @MFelix solution. That is much cleaner and removes any issues of new tests.

[PANDAmonium · Accepted solution]

I actually ran into a similar problem yesterday. So first make sure this is the way you want to model it vs splitting up employees and tests. If this is what you actually need, here are the steps...you have two options:

Option A (easy but requires multiple tables)

1. Duplicate your table for each of your tests.
2. On original table, remove duplicate rows on Employee
3. On original table, remove cols other than employee and job
4. On one of the new tables filter test to Test A.
5. Repeat for other two tables filtering on Test B and Test C
6. Merge Test A into your original table on employee = employee.
7. Expand Status and Dates column leaving the prefix of the table name (Test A.) or something similar.
8. Repeat for Test B and C

Option B (more difficult but able to do with just one table)

1. Duplicate your test coluimn twice. So you will end up with 3 Test rows.
2. Highlight one of the Test column and the Status columns then select select Pivot Columns w Status as the value and under advanced options select Don't Aggregate.
3. This will create additional cols for Test A, Test B, Test C. So rename each to Test A Status, Test B Status, Test C Status.
4. Repeat steps 2 and 3 for Date1 and Date2
5. Group Rows with a group by of Employee and Job and aggregations for each of your new columns with an operation of Max.

The downside with both these solutions is if there is ever a Test D you will have to write that into the query.

[Anonymous]

The multiple table scenario worked thank you!