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"
Hello Anonymous,
What I meant is if you check the result that the query gives the first issue is it starts with 2015.jan when the first date in the table is 2015.sept
Also if you check the months they are missing, from 2016 until 2021 every year has data from january until december but the table gave back jan-feb-dec for every 2016 year apparence even when the year is alredy 2018 or later. In 2017 the dates are feb-apr-sep-oct-nov-dec all the other months are missing and overwrited by the earlier mentioned dates. It doesn't reach 2018 just repeats between 2015 and 2017 with the missing months.
Marked the repeating sequence.
Hi mmarton
Can you provide an Excel export with your input table and your Power BI file ? It will be easier to understand