Convert Excel columns into rows in a PBI table

Hi ldwf,

1. Load your excel file to the Power BI
2. 

    

3. Open Transform Data
4. Select the Ticket ID column.
5. Choose Transform --> Unpivot Other Columns

6. You'll get columns like: Ticket ID, Attribute, Value

7. 

    

8. Filter the Value column to keep only values > 0
9. Rename Attribute to Country
10. 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.