Reduce Date Table Size?

Power BI Data Model
1

The M code generates more date columns than I actually need. Is it wise to run the code and then delete the columns I don’t need? Or does it matter? Is there a way to use the M code to create the date table, delete the columns I don’t need, and somehow harvest that code to use in the future? Any thoughts are appreciated.

2

Hi @talk2gwhite. Yes, just use the “Choose Columns” button/icon in the Power Query toolbar to select only the columns you want … the data does not get loaded into your model until you hit “Close & Apply”.
Greg

3

Got that part, but is there a way to capture the script to create the resulting table in the future? For instance, if the date table produces 30 columns, but I only need 15, is there a way to capture the script to use in the future and/or pass on to colleagues?

4

You may want to edit the M code directly in the Advanced Editor to include only the columns you want … you can then email the M code. Or you can create a template app with the date table columns as you want and distribute a “starting point” PBIX … whatever works best for you. (You can also copy-and-paste the query from one PBIX to another if you have 2 open at the same time on your machine.)
Greg

1 Like
5

Hi @talk2gwhite,

Like @Greg mentioned, think the best way to do this is:

  1. on the invoked date function query do a Choose Columns step.
  2. copy the code for that step from the formula bar
  3. move back to the date table function & open the advanced editor (scrol to the end)
  4. move to the end of the last line InsertChangedType and add a comma at the end, hit enter
  5. create a new variable name something like: SelectColumns = and past in the code you’d copied
  6. in the code you just pasted replace the Source reference with InsertChangedType
  7. after the first in clause replace the InsertChangedType step by the new SelectColumns
  8. move back to your invoked function query and delete the Removed other columns step

Think that should do it.
I hope this is helpful.

2 Likes
6

Hi @talk2gwhite, did the response provided by @Melissa and @Greg help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

7

Hi @talk2gwhite, we’ve noticed that no response has been received from you since the 26th of February. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

8

Hi there,
There are different kinds of reduction techniques.

Please follow the link below.

9

Hi @talk2gwhite, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!