Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

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
PANDAmonium
Resolver III
Resolver III

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

MFelix
Super User
Super User

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

 

 

 


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
MFelix
Super User
Super User

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

 

 

 


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



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
Resolver III
Resolver III

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
Not applicable

The multiple table scenario worked thank you!

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.