CODEWITHSUNDEEP
excelpowerbipowerquery
sebastian wei
sebastian wei

Reputation: 81

How can I pivot a column in Power Query and keep order?

Original data:

enter image description here

I want to transform them like this:

enter image description here

I tried to pivot it in Power Query. But the order is not correct. The column with empty value would fill up:

enter image description here

Upvotes: 0

Views: 1355

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

Since your Measurement ID's are numeric and sequential within each series

  • Add a 1-based index column.
  • Then add a custom column
    • Formula = [Index]-[Measurement ID]
    • If the ID sequence is broken, the formula will return a different result.

enter image description here

If the Measurement ID's in your actual data do not fit that pattern, it should be relatively easy to create an equivalent index that does match that pattern, and then use the same algorithm

Now, when you Pivot, you will get your desired outcome.

M Code

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Measurement ID", Int64.Type}, {"Measurement Result", type number}}),
    
    #"Added Index" = Table.AddIndexColumn(
        #"Changed Type", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", 
        each [Index]-[Measurement ID]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}),
    
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {
        {"Measurement ID", type text}}, "en-US"), 
        List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {
            {"Measurement ID", type text}}, "en-US")[#"Measurement ID"]), "Measurement ID", "Measurement Result"),
    
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Custom"})
in
    #"Removed Columns1"

enter image description here

enter image description here

If your Measurement ID column is not in the designated pattern

  • I make the assumption that each Series starts with the first ID in the column.

To create our Custom series, we can then use (after inserting the Index column),

  • a formula that returns an Index number if the value in the ID column is the same as the first, otherwise return a null
  • Then 'Fill Down'
   #"Added Custom" = Table.AddColumn(#"Added Index", "sequence", 
       each if [Measurement ID] = #"Added Index"[Measurement ID]{0} then [Index] else null),
   #"Filled Down" = Table.FillDown(#"Added Custom",{"sequence"}),
   #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Index"}),

Upvotes: 2

Unbridledscum
Unbridledscum

Reputation: 44

It looks like you expect Power Query to implicitly know that Measurement ID 4 belongs to a 2nd set of data?

It won't do that for you unless you specify whether each measurement belongs to a 1st, 2nd or 3rd set.

You could:

  • Write the set IDs in manually to a new column
  • Calculate them programatically e.g New column with value that increments +1 whenever the current measurement ID is less than the previous measurement ID
  • Go back to the source data and check if you can have Measurement ID 4 = null in the 1st and 3rd sets.

For instance, with the third option your table would perhaps resemble:

Set ID Result
1 1 a
1 2 b
1 3 c
1 4 null
2 1 d
2 2 e
2 3 f
2 4 g
3 1 h
3 2 i
3 3 j
3 4 null

There isn't enough information about your data, therefore the details & the correct solution need to be left to you.

Upvotes: 1

Related Questions