Create a calculated table while looping on given table.

[salmanlari]

Hi all,

I am trying to create a table from given table, but with more than one column at a time, so that I can get the below result.

Given Table:

From date      To Date        Resource Name    Customer Name

4/26/2017      4/28/2017   Resource 1            Customer 1

4/21/2017      4/22/2017   Resource 2            Customer 2

4/22/2017      4/22/2017   Resource 3            Customer 3

4/25/2017      4/26/2017   Resource 1            Customer 4

I want the below table: 

Date              Resource Name         Customer Name

4/26/2017     Resource 1                 Customer 1

4/27/2017     Resource 1                 Customer 1

4/28/2017     Resource 1                 Customer 1

4/21/2017     Resource 2                 Customer 2

4/22/2017     Resource 2                 Customer 2

4/22/2017     Resource 3                 Customer 3

4/25/2017     Resource 1                 Customer 4

4/26/2017     Resource 1                 Customer 4

I cannot figure out the way to calculate the second table above from the first table above. I tried finding FOR loop in DAX but failed. 

Please help me in this. Also, if there is any other easy way, then please suggest.

Thank you.

[MarcelBeug · Accepted solution]

It can easily be done in Power Query.

Watch this video to see how the following code was created:

let
    Source = Excel.Workbook(File.Contents("C:\Users\Marcel\Documents\Forum bijdragen\Power BI Community\Create a calculated table while looping on given table.xlsx"), null, true),
    GivenTable_Table = Source{[Item="GivenTable",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(GivenTable_Table,{{"From date", Int64.Type}, {"To date", Int64.Type}, {"Resource Name", type text}, {"Customer Name", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each {[From date]..[To date]}),
    #"Expanded Date" = Table.ExpandListColumn(#"Added Custom", "Date"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Date",{"From date", "To date"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Date", "Resource Name", "Customer Name"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"Date", type date}})
in
    #"Changed Type1"

[salmanlari]

Thanks brother. But will this solution be always dynamic? Because data will keep on adding on daily basis. I can't do this operation everytime a row is added.

[MarcelBeug]

Creating the query is a one time exercise. Once that's done, you just need to refresh the data.