Need help transforming this excel file

[kkoc3karan]

Hi Guys 

I am new to M query and i am trying to transform this excel to tabular format so that i can use it in my report 

Sample file:-  https://docs.google.com/spreadsheets/d/1KC_gTPzu8uZIA9XUrK8JaB2mlh-YOqhv/edit?usp=sharing&ouid=10944...

Looks like this 

Output i am expecting is something like this 

[v-jingzhang · Accepted solution]

Hi @kkoc3karan 

Here is my code. You can create a blank query, open its Advanced Editor, paste below code into it to replace everything there. Then in the first Source step, replace the xxxxxxxxx file path with the same Excel file path on your local machine. Notice that I transform the sheet "2023" for example. 

let
    Source = Excel.Workbook(File.Contents("C:\xxxxxxxxxxxxxxxxxxxx\Companion Animals.xlsx"), null, true),
    #"2023_Sheet" = Source{[Item="2023",Kind="Sheet"]}[Data],
    #"Removed Top Rows" = Table.Skip(#"2023_Sheet",2),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([Column1] <> null) and ([Cat] <> "January")),
    Custom1 = #table(
        List.FirstN(Table.ColumnNames(#"Filtered Rows"), 1) & {"Cat", "Dog"}, 
        List.TransformMany(Table.ToRows(#"Filtered Rows"), each List.Split(List.Skip(_, 1), 2), (x, y)=> List.FirstN(x, 1) & y)
        ),
    #"Added Index" = Table.AddIndexColumn(Custom1, "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Period Index", each Number.Mod([Index],12)),
    #"Added Conditional Column" = Table.AddColumn(#"Added Custom", "Period", each if [Period Index] = 1 then "January" else if [Period Index] = 2 then "February" else if [Period Index] = 3 then "March" else if [Period Index] = 4 then "April" else if [Period Index] = 5 then "May" else if [Period Index] = 6 then "June" else if [Period Index] = 7 then "July" else if [Period Index] = 8 then "August" else if [Period Index] = 9 then "September" else if [Period Index] = 10 then "October" else if [Period Index] = 11 then "November" else "December"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"Index", "Period Index"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1", "Category"}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Category", "Period"}, "Attribute", "Value"),
    #"Renamed Columns1" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Animal"}})
in
    #"Renamed Columns1"

In addition, please add some values to your tables in the Excel file to test the code, otherwise the output will be an empty table after transformations as the values are all null in your current file. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.