Pivoting Rows to Column

Power BI Data Model
1

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?

2

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

3

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

4

@Melissa

image
image868Ă—142 4.5 KB

to

image
image1197Ă—125 4.86 KB

The above illustration is what i am trying to achieve,

Will appreciate your input

5

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
6

@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

7

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

8

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

image
image858Ă—204 6.11 KB

to

image
image1259Ă—214 6.9 KB

This is the right illustration

9

@Melissa Have you seen the new illustration?

10

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

11

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

12

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.

13

@Melissa Thanks, really appreciate it

14

Glad I could help :+1:

15

Hi @Melissa,
sorry for continuing this old topic, but I’m used to looking in the posts before issuing a new post.
I tried this solution of yours on a case of mine but I could not do it. The Record.ToList(_) didn’t work as I expected and I’m turning around
I need to pivot rows that repeat for some columns. Can the same approach work?

Thanks in advance

Roberto

image unpivoting.pbix (25.5 KB)

16

Hi @Roberto,

Great job on searching through the forum before asking a new question. :+1:

However if you do need support please always create a new topic and avoid asking new questions in already closed topics. You can link to that original topic if its relevant to your new question.

Furthermore please always include a data source file (like a xlsx for example) when posting Power Query questions.

Thank you!