Unpivot paired columns

[DDON]

Hi,

I am looking to unpivot multiple pairs of columns into 2 columns.

The image below shows a simplified dataset, in each row the value in f[n] relates to the value in S[n], in the actual dataset [n] runs from 1-27 and there are multiple IMEI (serial) numbers.

I would like to bring the data together with 1 row for each f[n] S[n] column pair:

I am sure there is some elegant unpivoting/pivoting solution but it eascapes my grasp.

Any help would be very much appreciated.

[Jimmy801 · Accepted solution]

Hello @DDON 

you can add a new column with this formula, delete all your S and F-fields and then expand the list to rows and then expand the record

let 
            CreateNumber = List.Numbers(0,3),
            TransformToValuesFS = List.Transform 
            (
                CreateNumber,
                (item)=>   [F=  try Record.Field(_, "F[" & Text.From(item) & "]") otherwise null , S= try Record.Field(_, "S[" & Text.From(item) & "]") otherwise null] 
            ),
            CleanNulls = List.Select(TransformToValuesFS, each _[F] <> null and _[S]<>null)
        in 
            CleanNulls

This is quite complex you have to pay attention on the following

Adapt this number to your maximum numbers of pairs   --> CreateNumber = List.Numbers(0,3)

Adapt this text according your standard naming of your F and S-columns

(item)=> [F= try Record.Field(_, "F[" & Text.From(item) & "]") otherwise null , S= try Record.Field(_, "S[" & Text.From(item) & "]") otherwise null]
)

Here the complete code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLWMzDSMzJU0lFKTEoGkiCWERAbA7GJUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DateTime = _t, IMEI = _t, #"F[0]" = _t, #"F[1]" = _t, #"S[0]" = _t, #"S[1]" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DateTime", type date}, {"IMEI", type text}, {"F[0]", Int64.Type}, {"F[1]", Int64.Type}, {"S[1]", Int64.Type}, {"S[0]", Int64.Type}}, "de-DE"),
    AddPairs = Table.AddColumn(#"Changed Type", "Pairs", each let 
            CreateNumber = List.Numbers(0,3),
            TransformToValuesFS = List.Transform 
            (
                CreateNumber,
                (item)=>   [F=  try Record.Field(_, "F[" & Text.From(item) & "]") otherwise null , S= try Record.Field(_, "S[" & Text.From(item) & "]") otherwise null] 
            ),
            CleanNulls = List.Select(TransformToValuesFS, each _[F] <> null and _[S]<>null)
        in 
            CleanNulls),
    #"Removed Other Columns" = Table.SelectColumns(AddPairs,{"DateTime", "IMEI", "Pairs"}),
    #"Expanded Pairs" = Table.ExpandListColumn(#"Removed Other Columns", "Pairs"),
    #"Expanded Pairs1" = Table.ExpandRecordColumn(#"Expanded Pairs", "Pairs", {"F", "S"}, {"F", "S"})
in
    #"Expanded Pairs1"

this transforms this

into this

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Mehdi]

Thank for sharing. I have just used your solution to help on another forum

[DDON]

Many thanks for the solution - I learnt a lot from seeing how you went about this!

[ImkeF]

Hi @DDON ,

1) Check columns "DateTime" and "IMEI" and unpivot other columns

2) Copy function to remove text between delimiters: Text.RemoveBetweenDelimiters function for Power BI and Power Query – The BIccountant

3) Add a new column where you apply this function to column "Attributes" (this column will be created by the unpivot action). This should return a new column with the desired new column names.

4) Delete column Attributes and pivot on the newly created column.