Forum Discussion
undefined
I have a problem regarding adding several values for each row in a table in power BI. I created sample table to explain what I want to do. This is the sample table of the Candidates and date table ...
Hi Anonymous ,
According to your description, here are my steps you can follow as a solution.
(1) My test data is the same as yours.
(2) Click "transform data" to enter the power query editor, click "Advanced Editor", copy and paste the following code. Please check the steps in the Step column on the right.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY7BCoAgEET/Zc9BaUVdo44FQUfxsNQSUhiY/09qhEW3t+ybYYQABgkwlrKUZ5w7Lm/MHQ6oVzIOOtSKdpCJgJfisY7YKUOzPbw/ocFg+5bq1+2xHZuQQX2bRTTzL/aESxhjtmCW7uDx/do7zYq0Vad9JoOUFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Application = _t, #"Open Date Application" = _t, #"Closed Date Application" = _t, Job = _t, Recruiter = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Application", Int64.Type}, {"Open Date Application", type date}, {"Closed Date Application", type date}, {"Job", type text}, {"Recruiter", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {Number.From([Open Date Application])..Number.From([Closed Date Application])}), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}}), #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type1", {{"Custom", type text}}, "en-US"), "Custom", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3"}), #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", Int64.Type}, {"Custom.2", Int64.Type}, {"Custom.3", Int64.Type}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type2", each ([Custom.2] = null or [Custom.2] = 1)), #"Inserted Merged Column" = Table.AddColumn(#"Filtered Rows", "Merged", each Text.Combine({Text.From([Custom.3], "en-US"), Text.From([Custom.1], "en-US"), Text.From([Custom.2], "en-US")}, "/"), type text), #"Removed Columns" = Table.RemoveColumns(#"Inserted Merged Column",{"Custom.1", "Custom.2", "Custom.3"}), #"Changed Type3" = Table.TransformColumnTypes(#"Removed Columns",{{"Merged", type date}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type3",{{"Merged", "New Column to join Date table"}}) in #"Renamed Columns"(3) Then the result is as follows.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
According to your description, here are my steps you can follow as a solution.
(1) My test data is the same as yours.
(2) Click "transform data" to enter the power query editor, click "Advanced Editor", copy and paste the following code. Please check the steps in the Step column on the right.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY7BCoAgEET/Zc9BaUVdo44FQUfxsNQSUhiY/09qhEW3t+ybYYQABgkwlrKUZ5w7Lm/MHQ6oVzIOOtSKdpCJgJfisY7YKUOzPbw/ocFg+5bq1+2xHZuQQX2bRTTzL/aESxhjtmCW7uDx/do7zYq0Vad9JoOUFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Application = _t, #"Open Date Application" = _t, #"Closed Date Application" = _t, Job = _t, Recruiter = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Application", Int64.Type}, {"Open Date Application", type date}, {"Closed Date Application", type date}, {"Job", type text}, {"Recruiter", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {Number.From([Open Date Application])..Number.From([Closed Date Application])}),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}}),
#"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type1", {{"Custom", type text}}, "en-US"), "Custom", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", Int64.Type}, {"Custom.2", Int64.Type}, {"Custom.3", Int64.Type}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type2", each ([Custom.2] = null or [Custom.2] = 1)),
#"Inserted Merged Column" = Table.AddColumn(#"Filtered Rows", "Merged", each Text.Combine({Text.From([Custom.3], "en-US"), Text.From([Custom.1], "en-US"), Text.From([Custom.2], "en-US")}, "/"), type text),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Merged Column",{"Custom.1", "Custom.2", "Custom.3"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Removed Columns",{{"Merged", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type3",{{"Merged", "New Column to join Date table"}})
in
#"Renamed Columns"(3) Then the result is as follows.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, Thanks for sharing your answer! Actually it works when I use this code with this sample table but when I just want to copy the same process on other dataset that I have imported the table, It doesn't work. Maybe, it is happening because I am not familiar with M code. I am trying to figure it out how I can use your code in my side. Thank you