Forum Discussion
Solved
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 ...
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"
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.