Forum Discussion

snowrider1799's avatar
snowrider1799
Icon for Helper I rankHelper I
6 years ago
Solved

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, 2...
Need Help
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago
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.
snowrider1799's avatar
snowrider1799
Icon for Helper I rankHelper I
6 years ago

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:

 

 

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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

    • snowrider1799's avatar
      snowrider1799
      Icon for Helper I rankHelper I
      6 years ago

      Hi Anonymous ,

       

      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. 

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

      Thanks Much!

       

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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