Forum Discussion

tgjones43's avatar
tgjones43
Helper IV
7 years ago
Solved

Row and column transformation

Hello

 

I'd like to do the transformation shown below in Power BI, i.e. have a single row for each "Number" value but split the number and letter values in column B into two columns. Would greatly appreciate any help with this.

 

Many thanks

Tim

 

Need Help
  • MFelix's avatar
    MFelix
    7 years ago

    Hi tgjones43,

     

    In the Power query add the following column:

    if (try Number.From([WFD]) otherwise 0) = 0 then "WFD Name" else "WFD Number"

    Then pivot columns without aggregation.

     

    See M code for full example:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAeJYHQjLEcwyArKM4CwnMMsYyDKGs5yVYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Number = _t, WFD = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Number", Int64.Type}, {"WFD", type text}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Custom", each if (try Number.From([WFD]) otherwise 0) = 0 then "WFD Name" else "WFD Number"),
    #"Pivoted Column" = Table.Pivot(#"Added Custom1", List.Distinct(#"Added Custom1"[Custom]), "Custom", "WFD")
in
    #"Pivoted Column"

    Regards,

    MFelix

     

  • MFelix's avatar
    MFelix
    7 years ago

    Hi tgjones43,

     

    Using the same logic you first need to do the Unpivot of your WFD colums, so you will get 2 columns Value and Attribute then just use the same formula with some adjustment o have the WFD 1 or 2 in your names. Check the M code for the column and the full data treatement.

     

    if (try Number.From([Value]) otherwise 0) = 0 then [Attribute]& " Name" else [Attribute] & " Number"

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAWMTpVgdCM8RiF3APCMgC4RN4TwnIHYF84yBLBA2g/OcgdhNKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Number = _t, WFD = _t, WFD2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Number", Int64.Type}, {"WFD", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Number"}, "Attribute", "Value"),
    #"Added Custom1" = Table.AddColumn(#"Unpivoted Columns", "Custom", each if (try Number.From([Value]) otherwise 0) = 0 then [Attribute]& " Name" else [Attribute] & " Number"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Attribute"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom]), "Custom", "Value")
in
    #"Pivoted Column"

    Regards,

    MFelix

3 Replies

  • MFelix's avatar
    MFelix
    Super User
    7 years ago

    Hi tgjones43,

     

    In the Power query add the following column:

    if (try Number.From([WFD]) otherwise 0) = 0 then "WFD Name" else "WFD Number"

    Then pivot columns without aggregation.

     

    See M code for full example:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAeJYHQjLEcwyArKM4CwnMMsYyDKGs5yVYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Number = _t, WFD = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Number", Int64.Type}, {"WFD", type text}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Custom", each if (try Number.From([WFD]) otherwise 0) = 0 then "WFD Name" else "WFD Number"),
    #"Pivoted Column" = Table.Pivot(#"Added Custom1", List.Distinct(#"Added Custom1"[Custom]), "Custom", "WFD")
in
    #"Pivoted Column"

    Regards,

    MFelix

     

    • tgjones43's avatar
      tgjones43
      Helper IV
      7 years ago

      Thank you so much MFelix, that helps a lot. Actually though, my data is a little more complicated. The transformation I want to do is shown below - there is a second column (column C, "WFD2") that needs transforming in the same way that column B was - is this possible?

       

      Tim

       

      • MFelix's avatar
        MFelix
        Super User
        7 years ago

        Hi tgjones43,

         

        Using the same logic you first need to do the Unpivot of your WFD colums, so you will get 2 columns Value and Attribute then just use the same formula with some adjustment o have the WFD 1 or 2 in your names. Check the M code for the column and the full data treatement.

         

        if (try Number.From([Value]) otherwise 0) = 0 then [Attribute]& " Name" else [Attribute] & " Number"

         

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAWMTpVgdCM8RiF3APCMgC4RN4TwnIHYF84yBLBA2g/OcgdhNKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Number = _t, WFD = _t, WFD2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Number", Int64.Type}, {"WFD", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Number"}, "Attribute", "Value"),
    #"Added Custom1" = Table.AddColumn(#"Unpivoted Columns", "Custom", each if (try Number.From([Value]) otherwise 0) = 0 then [Attribute]& " Name" else [Attribute] & " Number"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Attribute"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom]), "Custom", "Value")
in
    #"Pivoted Column"

        Regards,

        MFelix