Forum Discussion
Date column reconstruction
- Anonymous5 years ago
I modified the query code and it returns the following result :
let
Forrás = Excel.Workbook(File.Contents("C:\Users\XXXXXXX\Downloads\OriginalDateFormat.xlsx"), null, true),
Sheet2 = Forrás{[Name="Sheet1"]}[Data],
#"Típus módosítva" = Table.TransformColumnTypes(Sheet2,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}}),
#"Első sorok eltávolítva" = Table.Skip(#"Típus módosítva",1),
#"Előléptetett fejlécek" = Table.PromoteHeaders(#"Első sorok eltávolítva", [PromoteAllScalars=true]),
#"Típus módosítva1" = Table.TransformColumnTypes(#"Előléptetett fejlécek",{{"Row Labels", type text}, {"x", Int64.Type}, {"y", Int64.Type}, {"z", Int64.Type}, {"a", Int64.Type}, {"B", Int64.Type}, {"c", Int64.Type}, {"d", Int64.Type}, {"Grand Total", Int64.Type}}),
#"Érték felülírva" = Table.ReplaceValue(#"Típus módosítva1",null,0,Replacer.ReplaceValue,{"Row Labels", "x", "y", "z", "a", "B", "c", "d", "Grand Total"}),
#"Típus módosítva2" = Table.TransformColumnTypes(#"Érték felülírva",{{"x", Int64.Type}, {"y", Int64.Type}, {"z", Int64.Type}, {"a", Int64.Type}, {"B", Int64.Type}, {"c", Int64.Type}, {"d", Int64.Type}, {"Grand Total", Int64.Type}}),
#"Added Conditional Column" = Table.AddColumn(#"Típus módosítva2", "Year", each if Text.Contains([Row Labels], "0") then [Row Labels] else if Text.Contains([Row Labels], "1") then [Row Labels] else if Text.Contains([Row Labels], "2") then [Row Labels] else if Text.Contains([Row Labels], "3") then [Row Labels] else if Text.Contains([Row Labels], "4") then [Row Labels] else if Text.Contains([Row Labels], "5") then [Row Labels] else if Text.Contains([Row Labels], "6") then [Row Labels] else if Text.Contains([Row Labels], "7") then [Row Labels] else if Text.Contains([Row Labels], "8") then [Row Labels] else if Text.Contains([Row Labels], "9") then [Row Labels] else null),
#"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Year"}),
#"Added Conditional Column1" = Table.AddColumn(#"Filled Down", "RemoveYearRows", each if [Row Labels] = [Year] then 1 else 0),
#"Filtered Rows" = Table.SelectRows(#"Added Conditional Column1", each ([RemoveYearRows] = 0)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"RemoveYearRows"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns", "Year. Month", each [Year]&". "&[Row Labels]),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Year"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Row Labels", "Year. Month", "x", "y", "z", "a", "B", "c", "d", "Grand Total"})
in
#"Reordered Columns"
I modified the query code and it returns the following result :
let
Forrás = Excel.Workbook(File.Contents("C:\Users\XXXXXXX\Downloads\OriginalDateFormat.xlsx"), null, true),
Sheet2 = Forrás{[Name="Sheet1"]}[Data],
#"Típus módosítva" = Table.TransformColumnTypes(Sheet2,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}}),
#"Első sorok eltávolítva" = Table.Skip(#"Típus módosítva",1),
#"Előléptetett fejlécek" = Table.PromoteHeaders(#"Első sorok eltávolítva", [PromoteAllScalars=true]),
#"Típus módosítva1" = Table.TransformColumnTypes(#"Előléptetett fejlécek",{{"Row Labels", type text}, {"x", Int64.Type}, {"y", Int64.Type}, {"z", Int64.Type}, {"a", Int64.Type}, {"B", Int64.Type}, {"c", Int64.Type}, {"d", Int64.Type}, {"Grand Total", Int64.Type}}),
#"Érték felülírva" = Table.ReplaceValue(#"Típus módosítva1",null,0,Replacer.ReplaceValue,{"Row Labels", "x", "y", "z", "a", "B", "c", "d", "Grand Total"}),
#"Típus módosítva2" = Table.TransformColumnTypes(#"Érték felülírva",{{"x", Int64.Type}, {"y", Int64.Type}, {"z", Int64.Type}, {"a", Int64.Type}, {"B", Int64.Type}, {"c", Int64.Type}, {"d", Int64.Type}, {"Grand Total", Int64.Type}}),
#"Added Conditional Column" = Table.AddColumn(#"Típus módosítva2", "Year", each if Text.Contains([Row Labels], "0") then [Row Labels] else if Text.Contains([Row Labels], "1") then [Row Labels] else if Text.Contains([Row Labels], "2") then [Row Labels] else if Text.Contains([Row Labels], "3") then [Row Labels] else if Text.Contains([Row Labels], "4") then [Row Labels] else if Text.Contains([Row Labels], "5") then [Row Labels] else if Text.Contains([Row Labels], "6") then [Row Labels] else if Text.Contains([Row Labels], "7") then [Row Labels] else if Text.Contains([Row Labels], "8") then [Row Labels] else if Text.Contains([Row Labels], "9") then [Row Labels] else null),
#"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Year"}),
#"Added Conditional Column1" = Table.AddColumn(#"Filled Down", "RemoveYearRows", each if [Row Labels] = [Year] then 1 else 0),
#"Filtered Rows" = Table.SelectRows(#"Added Conditional Column1", each ([RemoveYearRows] = 0)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"RemoveYearRows"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns", "Year. Month", each [Year]&". "&[Row Labels]),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Year"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Row Labels", "Year. Month", "x", "y", "z", "a", "B", "c", "d", "Grand Total"})
in
#"Reordered Columns"
Anonymous Thank you! 🙂 Perfect output.
BA_Pete PaulDBrown Also thank you for your time and help 🙂