Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Find articles, guides, information and community news

Most Recent
v-yiruan-msft
Community Support
Community Support

Row and column conversion

Read more...

v-yiruan-msft
Community Support
Community Support

Scenario:
As shown in Table 1, each article has multiple features (rows), and now it needs each article display in one row with multiple feature columns. At the same time, the names of these feature columns need to be dynamically displayed as "Feature_" plus number, such as Feature_1, Feature_2 and Feature_3 etc. as shown in Table 2.

 

Please be aware that the data in Table 1 below is the up data, with multiple unique features for each article, and no duplicate rows in the table. If there are duplicated rows, it will cause confusion in "Grouped Rows" and generate index in Power Query later (for below: Detailed Steps - step 1).

 

Table 1:

yingyinr_12-1617780054390.png

Expected result: 

Table 2:

4.JPG

 

Detailed steps:   

Assume that the data has been loaded into Power BI. We need to make row and column conversion for the feature information of each article, so that only one row of data be displayed for each article.

1. Group by article name and add an index column(increment by 1) at the same time               yingyinr_0-1617779002468.png

 

2. Select the column Feature and Index to expand the table, uncheck the option “Use original column name as prefix”. Click “OK”

    yingyinr_1-1617779155768.pngyingyinr_2-1617779171686.png

 

3. Make row and column conversion for Feature information using Pivot column feature: Select Index column and set Feature    column (Don’t Aggregate) as Values column, click “OK”             yingyinr_3-1617779299000.png

yingyinr_4-1617779316845.png

So far, we have completed the row and column conversion of feature information. Next, we need to RENAME these converted feature columns in bulk (name them in Feature_1, Feature_2 and Featuer_3… form). We can achieve it by the following two methods.

Method 1:

Rename these feature columns in bulk

yingyinr_5-1617779358459.png

The full applied codes as follow:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSiwqyUzOSVUwVNJBsOPTUhNLDJVidfDJGxGQNyYgb0JA3pSAvBmKvBGSvBEW92PKGxGQR3W/MZK8MRbzMeVRzTdBkjfBot8USd6UKHmg+bEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Article = _t, Feature = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Article", type text}, {"Feature", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Article"}, {{"Index", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Index" = Table.ExpandTableColumn(#"Grouped Rows", "Index", {"Feature", "Index"}, {"Feature", "Index"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Index", {{"Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Expanded Index", {{"Index", type text}}, "en-US")[Index]), "Index", "Feature"),
    #"Renamed Columns" =Table.TransformColumnNames(#"Pivoted Column", each if Text.Upper(_)="ARTICLE" then _ else  "Feature_" & _) 
in
    #"Renamed Columns"

 

 

Method 2:

Add the following steps after step 2 in the previous row and perform column conversion process:

1. Add a custom column: Navigate to “Add Column” ribbon and click “Format” to select “Add prefix”. Input the value “Feature_” and click “OK” to save it.

yingyinr_6-1617779431044.png

  yingyinr_7-1617779457441.png

 

2. Delete the original Index column

yingyinr_8-1617779503025.png

 

3. Update the applied step pivot in the row and column conversion process: select Prefix column and set Feature column (Don’t Aggregate) as Values column, click “OK”       yingyinr_9-1617779544307.png

The full applied codes as follow:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSiwqyUzOSVUwVNJBsOPTUhNLDJVidfDJGxGQNyYgb0JA3pSAvBmKvBGSvBEW92PKGxGQR3W/MZK8MRbzMeVRzTdBkjfBot8USd6UKHmg+bEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Article = _t, Feature = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Article", type text}, {"Feature", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Article"}, {{"Count", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Table" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Feature", "Index"}, {"Feature", "Index"}),
    #"Inserted Prefix" = Table.AddColumn(#"Expanded Table", "Prefix", each "Feature_" & Text.From([Index], "en-US"), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Prefix",{"Index"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Prefix", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Prefix", type text}}, "en-US")[Prefix]), "Prefix", "Feature")
in
    #"Pivoted Column"

 

 

Extended content:

Sometimes we will encounter the following situations where we need to rename columns in bulk, how can we achieve this?

1. Each column name has the same prefix, which makes the column name look very long just like below table. We need to clear the prefix of these column names

You can add the below step into Advanced Editor using Text.Replace function:yingyinr_0-1617781605356.png

 

= Table.TransformColumnNames(#"Changed Type", (columnName as text) as text => Text.Replace(columnName, "ABCDEF_", ""))

 

yingyinr_10-1617779636098.png

 

2. The column name contains special characters, such as ”[]”,”{}” and “()” etc., as the table below, the user wants to keep only the content between “[]

yingyinr_1-1617781651687.png

The expected result as below:

3.JPG

You can add below step into Advanced Editor using Text.Contains and Text.BetweenDelimiters function:

 

=Table.TransformColumnNames(#"Changed Type" , each if Text.Contains(_,"[") then Text.BetweenDelimiters(_,"[","]") else _)

 

yingyinr_11-1617779717319.png

 

Author: Yingying Ruan 

Reviewer:  Kerry & Ula

Helpful resources

Join Blog
Interested in blogging for the community? Let us know.