Pivoting Rows to Column

Good Day,

Will like to pivot rows in a table to separate columns based on a conditional statement,

Please, who can help me with it or point me to materials that can guide me?

I expect this would be fairly easy… But still a small mock up file with desired result would go a long way here, can you supply that in excel?

Thanks

Hello @aroloyefolajimi,

Thank You for posting your query onto the Forum.

I’m providing a link below of the course - “Advanced Data Modelling and Transformations” where you can find the content specifically related to data modelling and how you can prepare and organize your data and can also download the resource files/materials from there.

Thanks & Warm Regards,
Harsh

@Melissa

to

The above illustration is what i am trying to achieve,

Will appreciate your input

Okay well this is a bit of an odd one because all these lines don’t share a common identifier, so you’ll need a solution for that. What I did was kept the Ticket number except for the last digit. 2 solutions.

Solution 1.

  • added the Ticket Group (Ticket number except for the last digit)
  • Unpivot Other Columns on Ticket Group
  • Transposed the table
  • Added an Index
  • Updated the Index with Number.Mod, to create a filter
  • Filtered the rows
  • Cleaned up the columns

.

Solution 2.

  • Added a custom column with Record.ToList(_)
  • Added the Ticket Group (Ticket number except for the last digit)
  • Grouped By the Ticket Group and amended the function to List.Combine(_[Record to List])
  • Extracted the values from the list
  • Split into columns by delimiter

Here’s my sample file. eDNA - Pivoting Rows to Columns.pbix (41.1 KB)
I hope this is helpful.

2 Likes

@Melissa Thanks for the help, the Ticket Column is meant to be the unique Identifier as other rows/columns are action taken on it.

Will download the Pbix File and check it out

That’s a relief.
Then just forget about the Ticket Group and instead use Ticket.

@Melissa Thanks for the help, made a mistake with the initial sample i sent

to

This is the right illustration

@Melissa Have you seen the new illustration?

Yes thanks @aroloyefolajimi .
Solution 2 is the way to go, here’s the updated M code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lY7BCsIwDIZfJfS8i9o3EC877jp2CO0/JtYGTXGvb6YDmQjSW77wJXx971jjuNsfXOMUOVIRamXKhicNnLgg2tzyFeqG5pfecZiQvg+WH1v/NuNOMytFyTDuoJIeL3t9sfVDEgWVc7igGB4XfLdkW3xcX5fuK9N9Zbr/nz48AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Ticket = _t, #"Action taken" = _t, Status = _t, Initiator = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Ticket", type text}, {"Action taken", type text}, {"Status", type text}, {"Initiator", type text}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Record to List", each Record.ToList(_)),
    #"Grouped Rows" = Table.Group(#"Added Custom1", {"Ticket"}, {{"Count", each List.Combine(_[Record to List])}}),
    #"Extracted Values" = Table.TransformColumns(#"Grouped Rows", {"Count", each Text.Combine(List.Transform(_, Text.From), "||"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Count", Splitter.SplitTextByDelimiter("||", QuoteStyle.Csv), {"Count.1", "Count.2", "Count.3", "Count.4", "Count.5", "Count.6", "Count.7", "Count.8", "Count.9", "Count.10", "Count.11", "Count.12", "Count.13", "Count.14", "Count.15", "Count.16"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Count.1", type text}, {"Count.2", type text}, {"Count.3", type text}, {"Count.4", type text}, {"Count.5", type text}, {"Count.6", type text}, {"Count.7", type text}, {"Count.8", type text}, {"Count.9", type text}, {"Count.10", type text}, {"Count.11", type text}, {"Count.12", type text}, {"Count.13", type text}, {"Count.14", type text}, {"Count.15", type text}, {"Count.16", type text}})
in
    #"Changed Type1"

.

and sample file eDNA - Pivoting Rows to Columns v2.pbix (37.5 KB)
I hope this is helpful

Thanks @Melissa it is useful, will try it out but i am wondering how it can be done with a Conditional Statement instead of using Extracted Values and Split Column by delimiter because of missing out some of the text when splitting it. Is it possible to say that If Status = Escalated, select the Action Taken and Initiators on new columns, If Status = Closed, select the Action Taken and Initiators on new columns and so on

Don’t think I follow…

Do you have nulls and is that causing some displacement? Then just add a step BEFORE Record.ToList replacing all nulls with some weird unique text value that you can remove after the last step, so that acts as a sort of placeholder during the transformations.

@Melissa Thanks, really appreciate it

Glad I could help :+1: