undefined

[Anonymous]

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 to explain what I want to get from this table.

I am wondering if anyone could help me to do this because it has been a long time I am stuck in this issue.

Application	Open Date Application	Closed Date Application	Job	Recruiter
1	Nov22	May23	Manger	Daniel
2	May22	Aug22	Director	Sara
3	Jan22	May22	CPA	Diana
4	Jan23	Jan23	Lead	Mark
5	Feb23	May23	Scientist	Daniel

Date

Jan22

Feb22

March22

April22

…….

I would like to get a graph that shows dates in X axis and the numbers of application which has been opened and still open that month and also have interactive graph with the filters(already have these filters in dashboard) of Job, Recruiters,…. In the dashboard view.

To be able to count all those months in which the application has been still open and be interactive with the filters, the only way I think I can do is making the table to add those months the application opened and still open as value in the last column for each application so it will happen to repeat the same application with all information and the custom column of months it is open. However, I don’t know how I can do it.

I want the results of modified table to be like this:

Application	Open Date Application	Closed Date Application	Job	Recruiter	New Column to join Date table
1	Nov22	May23	Manger	Daniel	Nov22
1	Nov22	May23	Manger	Daniel	Dec22
1	Nov22	May23	Manger	Daniel	Jan23
1	Nov22	May23	Manger	Daniel	Feb23
1	Nov22	May23	Manger	Daniel	March23
1	Nov22	May23	Manger	Daniel	April23
1	Nov22	May23	Manger	Daniel	May23
2	May22	Aug22	Director	Sara	May22
2	May22	Aug22	Director	Sara	Jun22
2	May22	Aug22	Director	Sara	Jul22
2	May22	Aug22	Director	Sara	Aug22

[Ashish_Mathur]

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.

[Anonymous · Accepted solution]

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. 

[Anonymous]

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

[Anonymous]

Hi @Anonymous ,

Please click the gear next to "Source" to modify the data source, taking care to keep the column names the same as the old data source.

Alternatively, you can follow these steps on the imported table:
1. Add a custom column.

2. Expand the column.

3. Change the column type to date type.

4. Split the column into year, month and day, filter the day column to keep only the 1 and null columns, and then merge again.

All these functions can be found in the function bar on the top side of power query, you can click the gear to see the more detailed settings.

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. 

[Anonymous]

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