Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hello everyone, I hope you are doing well. I have a challenging situation and i would like your help.
I have three columns. StartYear, EndYear, List as you see in the following image.
I neeed open the list and create a relation between years (Start and End) and the list (considering the sequence of the numbers).
You are able to see an example below.
StartYear: 2010
EndYear: 2020
List: (5,4,3,6,7,8,9,2,9,0,5)
The final return will be two columns as you see below:
2010,5
2011,4
2012,3
2013,6
2014,7
2015,8
2016,9
2017,2
2018,9
2019,0
2020,5
Could you help me with this challenging script?
Thank you
Solved! Go to Solution.
Hi, @wribeiro try this
let
Source = <your_source_table>,
type_int = Table.TransformColumnTypes(Source,{{"STARTYEAR", Int64.Type}, {"ENDYEAR", Int64.Type}}),
z = Table.Combine(Table.AddColumn(type_int, "Custom", each Table.FromColumns({{[STARTYEAR]..[ENDYEAR]} , [JSON_ARRAY]}))[Custom])
in
z
Hi, @wribeiro try this
let
Source = <your_source_table>,
type_int = Table.TransformColumnTypes(Source,{{"STARTYEAR", Int64.Type}, {"ENDYEAR", Int64.Type}}),
z = Table.Combine(Table.AddColumn(type_int, "Custom", each Table.FromColumns({{[STARTYEAR]..[ENDYEAR]} , [JSON_ARRAY]}))[Custom])
in
z
Just one more question. I wanna keep my old columns. Is it possible?
it is
let
Source = your_source_table,
type_int = Table.TransformColumnTypes(Source,{{"STARTYEAR", Int64.Type}, {"ENDYEAR", Int64.Type}}),
z = Table.ExpandTableColumn(Table.AddColumn(type_int, "Custom", each Table.FromColumns({{[STARTYEAR]..[ENDYEAR]} , [JSON_ARRAY]})),"Custom", {"Column1", "Column2"})
in
z
Amazing. Thank you
Incredible. Thank you
Hii @wribeiro ,
My workflow would look something like this:
1) Add / unfold all the years between StartDate and EndDate as new rows (see below screenshot)
2) Add an index for each row
3) Open the list
4) Add an index for each row of the list
5) Join the first index with the second index
6) Add column as concatenation of the previous join/merge statement.
To unfold all the rows between two dates you can use this one here:
I'd love to help ypu more, but it is hard to add that list example manually (rather, I was too lazy to create a nested JSON to imitate your case). Feel free to share the Power BI or JSON file in case you need more support 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
Thank you so much. I think... almost there. Could you give me a final explanation, please? I have attached my PowerBI sample file. In fact, I was expecting just 17 rows in this example.
https://drive.google.com/file/d/11gxDsEJrtCKAXcjh-AZzTthJ0LkOEUHl/view?usp=sharing
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
61 | |
40 | |
37 | |
28 | |
16 |