If you need to visualize your Amazon data in Power BI, here is one example.

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:

Expected result: 

Table 2:

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               

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

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”             

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

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.

2. Delete the original Index column

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”       

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:

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

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

The expected result as below:

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 _)

Author: Yingying Ruan 

Reviewer:  Kerry & Ula

To help you understand my blog, below is the Date Dimension which is marked as a Date Table in Power BI Desktop. This date table includes every date from 2016-2025. I do have more columns in my Date Dimension, but I only want to show the ones necessary for this example. As you can see, I have a Date Column and a Month Year column. For my report, only the Month and Year Column is needed for filtering.

Problem

Most of my reports at work are manually updated every month to reflect a rolling 13 months (Oct 2019 – Oct 2020) as shown above. Updating these reports to this setting was a pain, because I had to open and refresh all of my reports to do this. Often, I would spend 2 hours rolling all my reports forward. This quickly turned out to be burdensome and a waste of time, so I needed to find a way to have it update automatically. The challenge about these reports is the rolling 13 months needs to be displayed on the visualizations, but the filter needs to include other months so users can still slice through them. While researching this problem, I found solutions which pointed to using the relative date feature which works. But if you add the same month field to the filter pane, it will now show Oct 2019 – Oct 2020. This is a major drawback because my users in the Power BI Service would not be able to filter data on months outside of the rolling 13 months, as shown below.

I thought is there a way to use the relative date feature, but still allow my users to have access to the months outside of the rolling 13 months? I played with this feature and was able to come up with a trick. Below is my solution and instructions on how you can do the same.

Solution

1. In the Filter Pane, go to the Month Filter. In the “Filter Type” field, select Relative Date.

2. In the “Show items when the value:” fields please enter the following selections:

1st field - Is in the last

2nd field - 13

3rd field - months

3. Now select “Apply Filter”

4. Go back top field called "Filter type" and select Basic Filtering.

5. Now select Publish.

6. In the Power BI Service, your users will see the defaulted rolling 13 months on the report, and still be able to filter through the other months.

Conclusion

As you can see, our users can still navigate through the other months, but my reports are now showing a rolling 13 months.

Please let me know if this works for you, and if you currently implementing another solution to the problem! Also, please watch my video, which is a supplement to this blog.

Notes:

This trick was based on a specific business requirement. I only needed my data to be shown at the month level. Therefore, using the month field with the relative date filter worked.

I did notice one odd behavior worth mentioning:

1. On the Month Filter, the date range will display as 9/5/2019 – 10/4/2020. However, if you look at the visualization it shows October 2019 to October 2020.

Thank you for reading my blog.

You can view my video which is a supplement to this blog at: Quick Trick: Relative Date Filter in Power BI - YouTube

To learn more about me you can visit my page or connect on Social Media:  https://ITDATADIVA.com , (21) Priscilla (Marotte) Camp | LinkedIn , https://twitter.com/ITDataDiva,

With the buzz around Power BI Premium Per User Licensing going to General Availability at just $20 in April, this may be a time to witness an acceleration in Power BI Paginated reports development as it is included in PPU licensing and permits to host them in the PPU workspace. While the tool provides a host of functions often there may be a need to use your own code to address some of the business needs. Lets see in this post how to achieve that!