Forum Discussion
Convert Excel columns into rows in a PBI table
I have an Excel file that is the source of this report, and the data is structured as follows; i.e, the level of granularity is at the Ticket ID level. There are many other columns in the file, but these are the key ones:
I would like to create a PBI table that converts the data so that the level of granularity is at the Ticket ID and Country level, and I only want to show the contry name, i.e, without the word 'Area', and sometimes the word 'Area' can be before or after the country name:
The same Ticket ID can be part of multiple countries, and any country for which the cell value is 0 will not be in the table, which is why I don't want Brazil in the result. I would like to create a PBI table because the user would like to have a slicer on the country value. The number of countries is a finite set of six. Is there a way to write one DAX statement that transposes the columns into multiple rows, defines a new column called 'Country' with the values, or do I need to create a separate table for each country, then merge them all together into one result set?
Hi ldwf,
- Load your excel file to the Power BI
-
- Open Transform Data
- Select the Ticket ID column.
- Choose Transform --> Unpivot Other Columns
-
You'll get columns like: Ticket ID, Attribute, Value
-
- Filter the Value column to keep only values > 0
- Rename Attribute to Country
- Remove the word "Area" regardless of whether it's before or after the country name. Use a custom transformation below(Need to add directly in the Advance editor)
CleanCountry = Table.TransformColumns(#"Renamed Columns",{{"Country", each Text.Trim(Text.Replace(_, "Area", "")), type text}})11. Remove/Delete the Value column.
Total M-Code:
let Source = Excel.Workbook(File.Contents("C:\Users\ajayb\Downloads\SampleDat.xlsx"), null, true), #"Navigation 1" = Source{[Item = "Sheet1", Kind = "Sheet"]}[Data], #"Promoted headers" = Table.PromoteHeaders(#"Navigation 1", [PromoteAllScalars = true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted headers",{{"TicketID", Int64.Type}, {"Argentina Area", Int64.Type}, {"Chile Area", Int64.Type}, {"Brazil Area", Int64.Type}, {"Peru Area", Int64.Type}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"TicketID"}, "Attribute", "Value"), #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> 0)), #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Attribute", "Country"}}), CleanCountry = Table.TransformColumns(#"Renamed Columns",{{"Country", each Text.Trim(Text.Replace(_, "Area", "")), type text}}), #"Removed Columns" = Table.RemoveColumns(CleanCountry,{"Value"}) in #"Removed Columns"Thanks,
If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.
4 Replies
Hi ldwf,
- Load your excel file to the Power BI
-
- Open Transform Data
- Select the Ticket ID column.
- Choose Transform --> Unpivot Other Columns
-
You'll get columns like: Ticket ID, Attribute, Value
-
- Filter the Value column to keep only values > 0
- Rename Attribute to Country
- Remove the word "Area" regardless of whether it's before or after the country name. Use a custom transformation below(Need to add directly in the Advance editor)
CleanCountry = Table.TransformColumns(#"Renamed Columns",{{"Country", each Text.Trim(Text.Replace(_, "Area", "")), type text}})11. Remove/Delete the Value column.
Total M-Code:
let Source = Excel.Workbook(File.Contents("C:\Users\ajayb\Downloads\SampleDat.xlsx"), null, true), #"Navigation 1" = Source{[Item = "Sheet1", Kind = "Sheet"]}[Data], #"Promoted headers" = Table.PromoteHeaders(#"Navigation 1", [PromoteAllScalars = true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted headers",{{"TicketID", Int64.Type}, {"Argentina Area", Int64.Type}, {"Chile Area", Int64.Type}, {"Brazil Area", Int64.Type}, {"Peru Area", Int64.Type}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"TicketID"}, "Attribute", "Value"), #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> 0)), #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Attribute", "Country"}}), CleanCountry = Table.TransformColumns(#"Renamed Columns",{{"Country", each Text.Trim(Text.Replace(_, "Area", "")), type text}}), #"Removed Columns" = Table.RemoveColumns(CleanCountry,{"Value"}) in #"Removed Columns"Thanks,
If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.This is a great use case for Power Query's Unpivot feature rather than DAX. In Power Query: select your Ticket ID column, then select the country columns and choose Transform > Unpivot Columns (or right-click Ticket ID > Unpivot Other Columns). Rename the two new columns to Country and Count. Then add a step to clean the Country text - Transform > Format > Trim, plus a Replace Values step to remove the word Area (e.g. replace " Area" with nothing). Finally, filter the Count column to exclude 0 so Brazil-type rows drop out automatically. You'll end up with a clean Ticket ID/Country table that refreshes correctly every time the source data changes, and it's much more maintainable than a DAX transpose.
Hi ldwf ,
You don't need to create six seperate tables and merge them. Although you can do it with a single DAX calculated table using UNION, SELECTCOLUMNS, and FILTER, but i would recommend using Power Query , as this is data shaping task.
if you are creating a separate Ticket-country table-
-create a reference of the source query
-Keep only Ticket ID and the six country indicator columns.
-Select the country columns and choose Transform>Unpivot columns.
- Rename Attribute to country and value to country flag.
-Filter countryflag to keep value greater than 0
-Replace Area with an empty string in the country column then apply Trim.
This will give one row per Ticket ID -country combination that can be used in Country slicer.
ref- https://learn.microsoft.com/en-us/dax/union-function-dax
For a fixed set of six country column You can create calculated table -
TicketCountry = UNION ( SELECTCOLUMNS ( FILTER ( Tickets, Tickets[First country column ] > 0 ), "Ticket ID", Tickets[Ticket ID], "Country", "First country column"),SELECTCOLUMNS (FILTER ( Tickets, Tickets[ second country column] > 0 ),https://learn.microsoft.com/en-us/power-query/unpivot-column"Ticket ID", Tickets[Ticket ID],"Country", "second country column"),And then repeat the remaining four countries using the same pattern.This creates a single calculated table rather then six seperate tables. repeat the SELECTCOLUMNS section for remaining country columns. However power query Unpivot may be easier to maintain for this type of transformation.Hope this helps!Hi,
This M code works
let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Ticket ID"}, "Attribute", "Value"), #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> 0)), #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each Text.Trim(Text.Replace(Text.Upper([Attribute]),"AREA",""))), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value", "Attribute"}) in #"Removed Columns"Hope this helps.
