cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver I
Resolver I

Widen a Table

If I have a table like this:

image.png

How can I widen it into something like this:

image.png

Thank you for your help.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Resolver III
Resolver III

Re: Widen a Table

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.

View solution in original post

Highlighted
Super User III
Super User III

Re: Widen a Table

Hi @SKH19 ,

 

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

 

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

4 REPLIES 4
Highlighted
Resolver III
Resolver III

Re: Widen a Table

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.

View solution in original post

Highlighted
Resolver I
Resolver I

Re: Widen a Table

The multiple table scenario worked thank you!

Highlighted
Super User III
Super User III

Re: Widen a Table

Hi @SKH19 ,

 

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

 

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

Highlighted
Resolver III
Resolver III

Re: Widen a Table

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.

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors