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

edhans's avatar
edhans
Icon for Community Champion rankCommunity Champion
6 years ago

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.