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,
This M code works
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIw1PXLL9M1MoKwfRMrdY2MgWzfxLz01CIgwyUxLzM1RylWJ1oJWQ2U7ViaDmG7ZBalJpfkg3QEJxYlgtUbQ9R4Jeahmg9iOwc4gnUl5kHUmiCpNUZj+6QmpoCdVJQNVmsKkXdLTYKrhbs7ODkzNa8ks7gEyemxAA==", 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", "Month Span", each (12 * (Date.Year([Closed Date Application]) - Date.Year([Open Date Application])))
+ (Date.Month([Closed Date Application]) - Date.Month([Open Date Application]))
+ (if Date.Day([Closed Date Application]) < Date.Day([Open Date Application])
then -1
else 0
)
+ 1),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Month List", each List.Numbers(
1,
[Month Span]
)),
#"Expanded Month List" = Table.ExpandListColumn(#"Added Custom1", "Month List"),
#"Added Custom2" = Table.AddColumn(#"Expanded Month List", "Date", each Date.StartOfMonth(
Date.AddMonths(
[Closed Date Application],
0 - [Month Span] + [Month List]
)
)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Month Span", "Month List"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Date", type date}})
in
#"Changed Type1"Hope this helps.