Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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.
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.