Forum Discussion
How to create new columns based on value from another column
Hi all,
I would like to create a ew column where results are shown based on one value from a list of values from another column. For example, if there are 10 item numbers each for 7 regions, that would be 70 item numbers in total. But I would like to create 7 diferent columns, one for each region, like item number region A, item number region B, etc.. Please tell me how I can do it. Thank you.
Hi , Anonymous
According to your description, do you mean you want to convert your table like this?
If this , it can be realized in Power Query Editor.
We can put this M code in "Advanced Editor" to refer to :
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc+tDoAgFIDRd7mZIBfk5xGsVkZkzqAW338aZGPuq6edUmRt236dVowsdzusVPM3BXNgHmweTD8LYBEsgWUwOw3oOlpCJXSEnnDc+I6BMBImwgyo76g+", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Region = _t, Item = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Region", type text}, {"Item", type text}}), Custom1 = Table.Group(#"Changed Type","Region",{"test",(x)=>x[Item]}), Custom2 = Table.FromColumns(Custom1[test],Custom1[Region]) in Custom2Then we can get the table we want to :
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
3 Replies
Hello,
You could do a measure that counts the item numbers and then visualise this measure by region name so it would show you the count of item numbers for each region, instead of creating 7 columns.
If I answered your question, please mark my post as solution so it would appear to others, Appreciate your Kudos👍
Thank you for your answer Idrissshatila .I actually need to create columns because that is the requirement of the customer, as they want to see separate lists of item numbers in a table format, specific to each region. Hence the question.
Hi , Anonymous
According to your description, do you mean you want to convert your table like this?
If this , it can be realized in Power Query Editor.
We can put this M code in "Advanced Editor" to refer to :
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc+tDoAgFIDRd7mZIBfk5xGsVkZkzqAW338aZGPuq6edUmRt236dVowsdzusVPM3BXNgHmweTD8LYBEsgWUwOw3oOlpCJXSEnnDc+I6BMBImwgyo76g+", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Region = _t, Item = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Region", type text}, {"Item", type text}}), Custom1 = Table.Group(#"Changed Type","Region",{"test",(x)=>x[Item]}), Custom2 = Table.FromColumns(Custom1[test],Custom1[Region]) in Custom2Then we can get the table we want to :
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
