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 mmarton
I am not sure I understood. If you have duplicates you can remove duplicates from the final column.
That is normal if you don't have 2021 in your input to not have it in your final column. If you want to have exactly your result with the format you presented in your first post :
- Introduce a new step (as second last step, between Removed Columns & Added Custom steps) where you replace the value in the column1 : jan by January, feb by February...and so on ;
- Replace the last step with this one : #"Added Custom" = Table.AddColumn(#"Removed Columns", "Year&Month", each [Conditionnal1]&". "&[Column1])
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.
- Anonymous5 years agoNot applicable
Hi mmarton
Can you provide an Excel export with your input table and your Power BI file ? It will be easier to understand