Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Community,
I have one query.
Does anybody know how Can I have a same set or same number of values for each row.
I will explain it better with an example.
Suppose I have a column named as "KEY" in a table named as "Date".
I want to have year from 2022 to next 4 years i.e. till 2026 for each key. Like this:
I have tried doing this, But it is not working:
let
Source = Table.FromList(List.Distinct(#"Full BECF"[KEY])),
StartYear = 2022,
EndYear = 2026,
NumberofYears = EndYear-StartYear,
Years = List.Generate(
() => [i=0, year = StartYear],
each [i] < NumberofYears,
each [i=[i]+1,year = [year]+1],
each [year]
),
Converted = Table.FromList(Years,Splitter.SplitByNothing(),{"Year"},null,ExtraValues.Error),
Merged = Table.NestedJoin(Source,{"Column1"},Converted,{"Year"},"NewColumn",JoinKind.FullOuter),
Expanded = Table.ExpandTableColumn(Merged,"NewColumn",{"Year"},{"NewColumn.Year"})
in
Expanded
Can you guys help me in achieving this.
Much Much thanks in Advance.
Solved! Go to Solution.
Start with your intial table and add a custom column with this expression
= {2022..2026}
Then hit the expand button in the column header to expand that list to new rows.
Pat
Start with your intial table and add a custom column with this expression
= {2022..2026}
Then hit the expand button in the column header to expand that list to new rows.
Pat
@ppm1 Thanks a lot.
Crazy, how much time I was wasting instead of this simple code.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |