Forum Discussion
Helper IIHelp - 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 situation:
- I have a date table in PBI that covers every day between 2019 until 2022.
- I have imported an XLSX listing people by their rank.
- This XLSX details when they started that rank alongside listing previous ranks and when those were started too.
- I'm trying to output a table with people on one axis and dates on the other.
- With the table data being their current rank at that time.
Here's a visual idea of what I'm talking about: https://i.imgur.com/7cf17St.png
As you can see, the table on the left is not vertical which is what's tripping me up. If it was four columns: Who, Rank, Current/Previous, Start Date - then I think I can get it working. But that isn't what's needed in this situation and oh man, am I now lost in the code!
Can anyone figure out a way to create this table without having the reshape the data before importing it?
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:
2 Replies
- ImkeF
Community Champion
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:
- amitchandak
Super User
ImkeF , can you help on this
