PowerQuery: add multiple columns with no values based on list

Question

I have a few columns and rows created with the data i actually need to calculate and now i need to simply add a number of empty columns with specific names (i need a certain physical structure for later on in the Excel sheet); the amount and names are dynamic, i have them stored in a list - it's a set of dates.

Via various searching i came across the example below:

Custom = List.Accumulate({1..3}, Source, (state, current) => Table.AddColumn(state, Number.ToText(current), each null))

This indeed generates 3 (or what is inside the {...}) blank columns titled 1, 2 and 3. I replace the {...} content with my own list (which contains those various dates) and re-write this to the following:

List.Accumulate({MyList}, Source, (state, current) => Table.AddColumn(state, Date.ToText(current), each null))

However now this keeps giving me "We cannot convert a value of type List to type Date"; i'v tried changing the data type in MyList to text etc, then i get "We cannot convert a value of type List to type Text" etc... i must be missing something, i can't get this to work...

Any thoughts would be greatly appreciated!

horseyride · Accepted Answer · 2023-08-22 18:38:23Z

1

Your code works

x=MyList,  // for testing
Custom = List.Accumulate(MyList, Source, (state, current) => Table.AddColumn(state, Date.ToText(current), each null))
in Custom

That means the contents you are using for MyList is NOT a list of dates and you need to check it. See my test step x=MyList that I added right before the List.Accumulate step. Add similar code to yours and click on it. What does it show? Something like this?

edited Aug 22, 2023 at 18:38

answered Aug 22, 2023 at 0:35

horseyride

21.8k3 gold badges15 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

koen Over a year ago

Yea i played around a bit more and got it to work in the end, i made sure that inside my list my dates were in fact TEXT already and that did the trick. Thank you!

Collectives™ on Stack Overflow

PowerQuery: add multiple columns with no values based on list

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related