Dynamic Column generation challenge

[mstarr13]

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. 

[amitchandak]

@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"]}))

[parry2k]

@mstarr13 so you want 52 columns(2007 - 2058)?? It is bad design thou, not sure how you are planning to use these columns.

[mstarr13]

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. 

[parry2k]

@mstarr13 based on your feedback, you should be using year on rows not in columns and then you can visualize the way you want. This is better design rather than having years on columns.

[parry2k · Accepted solution]

@mstarr13 based on your example data table, it is perfect,  use matrix visual, put org on rows, years on columns and # procured on value and you will get what you are looking for.

Would appreciate Kudos 🙂 if my solution helped.