Forum Discussion
Create a calculated table while looping on given table.
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.
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"
3 Replies
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"- 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.
Creating the query is a one time exercise. Once that's done, you just need to refresh the data.
