Forum Discussion
Convert Excel columns into rows in a PBI table
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.
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 -