Forum Discussion
Solved
Help - Merge dates with Date Table
Hi there! I'm having some trouble with what I'm sure is a really easy problem to solve, but I think I've been working at it for so long now that I'm just going around in circles :C Here's the situ...
Hi StefanM ,
this is a version that can be done via the UI only.
Please paste the code into the advanced editor and follow the steps:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcixLLapU0lFyzCnISATSBob6hgb6RgZGBkCOU2oJWMwILharE60UlJmTWolVVkcJhGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Who = _t, #"Current Rank" = _t, Since = _t, Previous1 = _t, Since1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Who", type text}, {"Current Rank", type text}, {"Since", type date}, {"Previous1", type text}, {"Since1", type date}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Who"}, "Attribute", "Value"), #"Added Index" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index", 0, 1, Int64.Type), #"Inserted Integer-Division" = Table.AddColumn(#"Added Index", "Integer-Division", each Number.IntegerDivide([Index], 2), Int64.Type), #"Integer-Divided Column" = Table.TransformColumns(#"Inserted Integer-Division", {{"Index", each Number.Mod(_, 2), Int64.Type}}), #"Removed Columns" = Table.RemoveColumns(#"Integer-Divided Column",{"Attribute"}), #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Index", type text}}), #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type1", {{"Index", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Changed Type1", {{"Index", type text}}, "en-GB")[Index]), "Index", "Value"), #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Integer-Division"}), #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns1",{{"1", type text}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type2", each ([1] <> null)), #"Pivoted Column1" = Table.Pivot(Table.TransformColumnTypes(#"Filtered Rows", {{"1", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Filtered Rows", {{"1", type text}}, "en-GB")[#"1"]), "1", "0") in #"Pivoted Column1"Next time, please provide sample data that can easily be copied:
Hi StefanM ,
this is a version that can be done via the UI only.
Please paste the code into the advanced editor and follow the steps:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcixLLapU0lFyzCnISATSBob6hgb6RgZGBkCOU2oJWMwILharE60UlJmTWolVVkcJhGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Who = _t, #"Current Rank" = _t, Since = _t, Previous1 = _t, Since1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Who", type text}, {"Current Rank", type text}, {"Since", type date}, {"Previous1", type text}, {"Since1", type date}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Who"}, "Attribute", "Value"),
#"Added Index" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index", 0, 1, Int64.Type),
#"Inserted Integer-Division" = Table.AddColumn(#"Added Index", "Integer-Division", each Number.IntegerDivide([Index], 2), Int64.Type),
#"Integer-Divided Column" = Table.TransformColumns(#"Inserted Integer-Division", {{"Index", each Number.Mod(_, 2), Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Integer-Divided Column",{"Attribute"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Index", type text}}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type1", {{"Index", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Changed Type1", {{"Index", type text}}, "en-GB")[Index]), "Index", "Value"),
#"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Integer-Division"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns1",{{"1", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type2", each ([1] <> null)),
#"Pivoted Column1" = Table.Pivot(Table.TransformColumnTypes(#"Filtered Rows", {{"1", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Filtered Rows", {{"1", type text}}, "en-GB")[#"1"]), "1", "0")
in
#"Pivoted Column1"
Next time, please provide sample data that can easily be copied: