Fill in model year gaps with Power Query in Power BI

Question

I have a column for vehicle model years. It includes data with hyphens (e.g. 2016 - 2020). I would like to change it to a comma delimited list (2016, 2017, 2018, 2019, 2020). Ultimately, I will split it by the delimiter and unpivot all the columns (or split it by rows). I can't find anything on Google to help with that. Any help is appreciated.

Could you share your attempts or at least example data? (input and desired output in tabular form) — Michal Palko

dcbyers dcbyers · Accepted Answer · 2021-01-29T04:37:53

The following code starts with the hyphenated start/end years (don't sweat the 'Source' definition...just a table entered directly into PowerQuery editor).

The key steps are splitting the years on the dash and then generating a list based on the start and end years thus separated with the List.Generate function (#"List of Years" line). The magic statement is:

each let start = [StartYear], end = [EndYear] in List.Generate(() => start, each _ <= end, each _ + 1)

The 'let start = [StartYear], end = [EndYear]' is used to copy the value of the field for the respective row under consideration into the variables 'start' and 'end'. This is because the .Generate function is not capable of directly incorporating a field reference. The .Generate function then runs very much like a 'for' loop to create a list of values: initial value is 'start', continue iterating until the list value is less than or equal to 'end', increment by 1.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwNNM1MjAyUIqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Renamed Columns" = Table.RenameColumns(Source,{{"Column1", "HumanData"}}),
    #"HumanData Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"HumanData", type text}}),
    #"Split Years" = Table.SplitColumn(#"HumanData Type", "HumanData", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"StartYear", "EndYear"}),
    #"Year Types" = Table.TransformColumnTypes(#"Split Years",{{"StartYear", Int64.Type}, {"EndYear", Int64.Type}}),
    #"List of Years" = Table.AddColumn(#"Year Types", "ListOfYears", each let start = [StartYear], end = [EndYear] in List.Generate(() => start, each _ <= end, each _ + 1))
in
    #"List of Years"

Once in the list form, you have flexibility in how to expand in PowerQuery.

Hopefully this helps :).

Fill in model year gaps with Power Query in Power BI

1 Answers