cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Automatically generate columns in Power Query

Hi there, I'm trying to generate 20 columns in Power Query, automatically. 

 

The condition is always referring to current year (e.g. 2020), and create 20 columns of the year backward (e.g. 2019, 2018, 2017 until 1999). 

 

Is there a method to achieve the above in Custom col, M language/ DAX or functions? 

 

Thank you.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User I
Super User I

Re: Automatically generate columns in Power Query

Hi @snowrider1799 ,

 

This is a solution, but I agree with @Greg_Deckler , you may want to revisit the model, you may be better off using the migration to PBI as an opportunity to improve the model.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NcvJDcBACEPRXjiP5LBFSS2I/tsIMJnb15MdQQIGO60dL+UKcvgxOTb5lGmFXGPWu7aO324omMsm9ldhYJuv9SHzAw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"File Date" = _t, #"Expiry Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"File Date", type date}, {"Expiry Date", type date}}),

    //Plug in your source table here
    SourceTable = #"Changed Type",
    
    //This builds a list of years from the data
    ListOfYearsUsed = List.Sort(List.Transform(List.Combine({SourceTable[File Date], SourceTable[Expiry Date]}), Date.Year)),
    //Please feel free to replace with manual year range which looks like {2010..2020}
    ListOfYears = {ListOfYearsUsed{0}..List.Last(ListOfYearsUsed)},

    //This cycles through the list of years and add columns for each year
    #"Added Custom" = List.Accumulate(ListOfYears, SourceTable, (a, n)=> Table.AddColumn(a, Text.From(n), each if Date.Year([File Date]) <= Number.From(n) and Date.Year([Expiry Date]) > Number.From(n) then 1 else null, type number))
in
    #"Added Custom"

 

Kind regards,

JB

View solution in original post

8 REPLIES 8
Highlighted
Super User IV
Super User IV

Re: Automatically generate columns in Power Query

Hmm, I'll defer to @ImkeF and @edhans for the Power Query.

However, just off the cuff, that sounds like a bad idea, generally you are unpivoting things like that. Can I ask why you need 20 year columns??

If you could supply sample data and expected output I could probably get you a DAX solution.

---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Helper I
Helper I

Re: Automatically generate columns in Power Query

Hi Greg,

 

Yea, the user was very sophisticated in Excel, and currently we're trying to use PBI to generate the same result. That is why it feels like pivot and unpivot.

 

Here's the sample I'm attaching below:

2.JPG

 

 

The green columns is what generated manually in Excel, and it is needed for some calculation using the File Date and Expiration Date with the formula: ==IF(AND(YEAR($C4)>D$1,YEAR($B4)<=D$1),1,"").

 

I would appreciate if there are better approach to this. Thanks in advance.

Highlighted
Super User I
Super User I

Re: Automatically generate columns in Power Query

Hi @snowrider1799 ,

 

This is a solution, but I agree with @Greg_Deckler , you may want to revisit the model, you may be better off using the migration to PBI as an opportunity to improve the model.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NcvJDcBACEPRXjiP5LBFSS2I/tsIMJnb15MdQQIGO60dL+UKcvgxOTb5lGmFXGPWu7aO324omMsm9ldhYJuv9SHzAw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"File Date" = _t, #"Expiry Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"File Date", type date}, {"Expiry Date", type date}}),

    //Plug in your source table here
    SourceTable = #"Changed Type",
    
    //This builds a list of years from the data
    ListOfYearsUsed = List.Sort(List.Transform(List.Combine({SourceTable[File Date], SourceTable[Expiry Date]}), Date.Year)),
    //Please feel free to replace with manual year range which looks like {2010..2020}
    ListOfYears = {ListOfYearsUsed{0}..List.Last(ListOfYearsUsed)},

    //This cycles through the list of years and add columns for each year
    #"Added Custom" = List.Accumulate(ListOfYears, SourceTable, (a, n)=> Table.AddColumn(a, Text.From(n), each if Date.Year([File Date]) <= Number.From(n) and Date.Year([Expiry Date]) > Number.From(n) then 1 else null, type number))
in
    #"Added Custom"

 

Kind regards,

JB

View solution in original post

Highlighted
Super User II
Super User II

Re: Automatically generate columns in Power Query

This code will do that.

 

let
    Source = Date.Year(DateTime.Date(DateTime.LocalNow())),
    Custom1 = {Source-21..Source},
    #"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Transposed Table" = Table.Transpose(#"Converted to Table"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true])
in
    #"Promoted Headers"

Just put it in a blank query.

1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done

 

It does it based on this year. You could hardcode the 2020 if you want. My code will work in 2021 though if that is important to you.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Highlighted
Helper I
Helper I

Re: Automatically generate columns in Power Query

Hi @jborro ,

 

Thanks for the tricks, it works. I have a follow-up query regarding this.

 

Say now I have to group by a col call Company, and the years (1999 -- 2020) is the aggregation (SUM) I need to calculate. Are there any ways to do it in a single line? Instead of my manual insertion?

 

I would like to keep the 1999 to 2020 dynamic like Text.From(n) as the new column name. 

 

1.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Thanks Much!

 

 

 

 

Highlighted
Super User I
Super User I

Re: Automatically generate columns in Power Query

Yes. It is possible. In the same fashion as with column names - using List.Accumulate to generate a list if parameters for Table.Group. you can experiment yourself- i will post a.solutikn in 2-3 hours once I have access to PBI.

Kind regards
JB
Highlighted
Super User I
Super User I

Re: Automatically generate columns in Power Query

Hi @snowrider1799 ,

 

something like this:

 

let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NcvJDcBACEPRXjiP5LBFSS2I/tsIMJnb15MdQQIGO60dL+UKcvgxOTb5lGmFXGPWu7aO324omMsm9ldhYJuv9SHzAw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"File Date" = _t, #"Expiry Date" = _t]),
  #"Changed Type" = Table.TransformColumnTypes(Source, {{"File Date", type date}, {"Expiry Date", type date}}),
  // Plug in your source table here
  SourceTable = #"Changed Type",
  // This builds a list of years from the data
  ListOfYearsUsed = List.Sort(List.Transform(List.Combine({SourceTable[File Date], SourceTable[Expiry Date]}), Date.Year)),
  // Please feel free to replace with manual year range which looks like {2010..2020}
  ListOfYears = {ListOfYearsUsed{0}..List.Last(ListOfYearsUsed)},
  // This cycles through the list of years and add columns for each year
  #"Added Custom" = List.Accumulate(ListOfYears, SourceTable, (a, n)=> Table.AddColumn(a, Text.From(n), each if Date.Year([File Date]) <= Number.From(n) and Date.Year([Expiry Date]) > Number.From(n) then 1 else null, type number)),
  
  // This generates list parameter for Table.Group
  Parameters = List.Accumulate(ListOfYears, {}, (a,n)=> a & {{Text.From(n), (x)=> List.Sum(Table.Column(x, Text.From(n))), type number}}),
  #"Grouped rows" = Table.Group(#"Added Custom", {"Expiry Date"}, Parameters)
in
  #"Grouped rows"

 

NB: change "Expiry Date" in the #"Grouped rows" to "Ultimate Parent" as per your screenshot.

 

Kind regards,

JB

Highlighted
Helper I
Helper I

Re: Automatically generate columns in Power Query

Thank you @jborro , you've helped a bunch!

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Power BI Desktop August 2020 Update

Power BI Desktop August 2020 Update

We have great updates this month! Click the link for the video with more info.

Top Solution Authors
Top Kudoed Authors