Row and column transformation

[tgjones43]

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

[MFelix · Accepted solution]

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 · Accepted solution]

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]

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 · Accepted solution]

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