Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi community,
I would like to have the following input to be transformed in PowerQuery:
Starting number of output is defined, let's say, 400
Year | 2023 | 2024 | 2025 | 2026 | 2027 | 2028 | 2029 | 2030 | 2031 | 2032 |
Input | 0 | 2 | 5 | 1 | 0 | 1 | 3 | 0 | 0 | 4 |
Output | 400 | 402 | 407 | 408 | 408 | 409 | 412 | 412 | 412 | 416 |
It basically increments the starting number by the input number.
Can anyone help in getting DAX formula in Power Query for the above result.
Currently, I am just adding a custom column where output columns are [Year.1]
For eg. for column 2024.1, the expression is:
=[2024]+[2023.1]
For 2025.1,
=[2025]+[2024.1]
But this seems way to tedious considering I have high amount of data.
Can anyone help in providing DAX formula for the above results.
Solved! Go to Solution.
Hi @AdityaG
let
data_table = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8swrKC1R0lEyAGIjIDYFYkMoH0QbQ9kgbKIUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, #"2023" = _t, #"2024" = _t, #"2025" = _t, #"2026" = _t, #"2027" = _t, #"2028" = _t, #"2029" = _t, #"2030" = _t, #"2031" = _t, #"2032" = _t]),
source = Table.TransformColumnTypes(data_table,{{"Year", type text}, {"2023", type number}, {"2024", type number}, {"2025", type number}, {"2026", type number}, {"2027", type number}, {"2028", type number}, {"2029", type number}, {"2030", type number}, {"2031", type number}, {"2032", type number}}),
starting_number = 400,
input = Table.ToRows(source){0},
input_list = List.Buffer(List.Skip(input)),
count = List.Count(input_list),
output_list =
List.Generate(
() => [out = starting_number, i = 0],
(x) => x[i] <= count,
(x) => [out = x[out] + input_list{x[i]}, i = x[i] + 1],
(x) => x[out]
),
z = Table.FromRows({input, {"Output"} & List.Skip(output_list)}, Table.ColumnNames(source))
in
z
Hi @AdityaG
let
data_table = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8swrKC1R0lEyAGIjIDYFYkMoH0QbQ9kgbKIUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, #"2023" = _t, #"2024" = _t, #"2025" = _t, #"2026" = _t, #"2027" = _t, #"2028" = _t, #"2029" = _t, #"2030" = _t, #"2031" = _t, #"2032" = _t]),
source = Table.TransformColumnTypes(data_table,{{"Year", type text}, {"2023", type number}, {"2024", type number}, {"2025", type number}, {"2026", type number}, {"2027", type number}, {"2028", type number}, {"2029", type number}, {"2030", type number}, {"2031", type number}, {"2032", type number}}),
starting_number = 400,
input = Table.ToRows(source){0},
input_list = List.Buffer(List.Skip(input)),
count = List.Count(input_list),
output_list =
List.Generate(
() => [out = starting_number, i = 0],
(x) => x[i] <= count,
(x) => [out = x[out] + input_list{x[i]}, i = x[i] + 1],
(x) => x[out]
),
z = Table.FromRows({input, {"Output"} & List.Skip(output_list)}, Table.ColumnNames(source))
in
z
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
15 | |
15 | |
13 | |
12 | |
11 |