Forum Discussion
Date value connected to multiple values
HI Guys,
I'm new to PowerBI and trying some stuff. I have a question about an extra column where i'd put 3 deadline dates.
These deadline dates needs to connect with all my Batch numbers.
Example:
So, these deadline dates only have 3 options, but i want them to connect them automatic to the Batch numbers (also only 3 batch numbers, but connecte to a lot of projects)
I made this in Excel, so no data connections whatsoever.
Can someone help me?
Hi Baltabek1 ,
[Deadline Date connected to bach] is your expected result, right?
If so, you can duplicate the bacth number column and split it to get the numbers only. Then use the numbers to return the dates. Below is the whole codes, you can copy it into the blank query in Power Query, or just download my attachment to check.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckosSc5QMFSK1YGxjZDYxhB2SSJEPBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Batch number" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Batch number", type text}}), #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Batch number", "Batch number - Copy"), #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Batch number - Copy", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Batch number - Copy.1", "Batch number - Copy.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Batch number - Copy.1", type text}, {"Batch number - Copy.2", Int64.Type}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Batch number - Copy.1"}), #"Added Custom" = Table.AddColumn(#"Removed Columns", "Deadline Date", each Date.From(#date(2024,1,[#"Batch number - Copy.2"]))) in #"Added Custom"Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
I didnt caught your question, could you please clarify from where these dates come?
Hi Omar,
Thanks for answering. I've created these dates myself. But I need the dates to be connected to the 3 batches. And when a extra project with batch number 1 /2 or 3 will come, I need them to automaticly connect with these deadline dates again.
Can't really clarify good enough i guess..
Hi Baltabek1 ,
[Deadline Date connected to bach] is your expected result, right?
If so, you can duplicate the bacth number column and split it to get the numbers only. Then use the numbers to return the dates. Below is the whole codes, you can copy it into the blank query in Power Query, or just download my attachment to check.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckosSc5QMFSK1YGxjZDYxhB2SSJEPBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Batch number" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Batch number", type text}}), #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Batch number", "Batch number - Copy"), #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Batch number - Copy", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Batch number - Copy.1", "Batch number - Copy.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Batch number - Copy.1", type text}, {"Batch number - Copy.2", Int64.Type}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Batch number - Copy.1"}), #"Added Custom" = Table.AddColumn(#"Removed Columns", "Deadline Date", each Date.From(#date(2024,1,[#"Batch number - Copy.2"]))) in #"Added Custom"Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
