Forum Discussion

mmarton's avatar
mmarton
Regular Visitor
5 years ago
Solved

Date column reconstruction

Hello everyone,   Already tried a lot of thing but I'm not that experinced yet with Power Query. Does anyone have any advice? I have a Column in the format of the left one and want to achieve the...
  • Anonymous's avatar
    Anonymous
    5 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"