cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tgjones43 Regular Visitor
Regular Visitor

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

 

WFD.png

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Row and column transformation

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

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Super User
Super User

Re: Row and column transformation

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




3 REPLIES 3
Super User
Super User

Re: Row and column transformation

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

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




tgjones43 Regular Visitor
Regular Visitor

Re: Row and column transformation

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

 

WFD2.png

Super User
Super User

Re: Row and column transformation

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!