Forum Discussion
Dynamic Column generation challenge
So I am needing to build a table on the fly. First the structure then I am going to fill it. On the structure, I just want to insert a column for every year from 2007 through 2058. I was trying to do this with a List.Generate loop using the following code:
let
test_procurements_table = Source{[Name="test_procurements",Signature="table"]}[Data],
//create a table with years for columns
YearTable = #table({"Nation"},{}),
NationAircraftCountYears = List.Generate(
()=> [curYr=2007],
each [curYr]<2058,
each [curYr=[curYr]+1],
each YearTable = Table.AddColumn(YearTable,[curYr]))
in
YearTable
tried a few variants but at best I get the saingle column table with "Nation" as the only column rather than an empty table with the nation column plus a column for every value from 2007 to 2058.
5 Replies
yep in this case 52 columns. Once that table is built, it is going to be populated with values I have generated by reporting Procurment number by each of those years which I have summarized in another table by grouping on a Org identifier. So I get a breakdown of procurement numbers by Org for each of those years.
Imagine you have a table:
Org, Year, # procured
A 2007 12
A 2007 14
A 2008 8
A 2009 6
B 2007 10
B 2007 9
B 2009 5
and so on for 24 orgs, for years covering 2007-2028. They want to see in their report
Org 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019
A 26 8 6 0 0 0 0 0 0 0 0 0 0
B 19 0 5 0 0 0 0 0 0 0 0 0 0
and so on. This data comes out of a D365 OData Feed, structure incoming cannot be changed and does no support the types of metrics they want so I am attempting to build the structure on the fly to report against.
mstarr13 ,
can transpose help
https://docs.microsoft.com/en-us/powerquery-m/table-transpose
example
Table.Transpose(Table.FromRecords({[Name = "Full Name", Value = "Fred"], [Name = "Age", Value = 42], [Name = "Country", Value = "UK"]}))
