Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello!
Probably a very simple question, but I haven't found the answer yet.
I want to create a new tabel that is based on the values in a column of an existing table (Tbl_rent) and 'add' 5 rows for each value in the column Unit_Id (variable 'Scenario').
Thanks!
Jeroen
Tbl_Rent | NEW TABLE | ||||
Unit Id | Unit Id | Scenario | |||
1001001 | 1001001 | 1 | |||
1001002 | 1001001 | 2 | |||
1001003 | 1001001 | 3 | |||
1001004 | 1001001 | 4 | |||
1001005 | 1001001 | 5 | |||
1001006 | 1001002 | 1 | |||
1001007 | 1001002 | 2 | |||
1001008 | 1001002 | 3 | |||
1001009 | 1001002 | 4 | |||
1001010 | 1001002 | 5 | |||
1001011 | 1001003 | 1 | |||
1001003 | 2 | ||||
1001003 | 3 | ||||
1001003 | 4 | ||||
1001003 | 5 | ||||
1001004 | 1 |
Solved! Go to Solution.
@JeroenN , Test as
Crossjoin(all(Tbl_Rent[Unit Id]), generateseries(1,5))
Hi @JeroenN
Another solution in Power Query for good measure.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwACGlWB0Y2wiJbYzENkFimyKxzZDY5khsCyS2JYJtaIDEBtobCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Unit Id" = _t]),
#"Added Custom" = Table.AddColumn(Source, "Scenario", each {1..5}),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Scenario")
in
#"Expanded Custom"
Regards
Phil
Proud to be a Super User!
Hi @JeroenN
This can be done in DAX as well, but I would recommend to do it in PQ. Place the following M code in a blank query to see the steps.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwACGlWB0Y2wiJbYzENkFimyKxzZDY5khsCyS2JYJtaIDEBtobCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Unit Id" = _t]),
#"Added Custom" = Table.AddColumn(Source, "Scenario", each List.Numbers(1,5)),
#"Expanded Scenario" = Table.ExpandListColumn(#"Added Custom", "Scenario")
in
#"Expanded Scenario"
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
@JeroenN
Create a new table with the following code:
New Table =
GENERATE(
Table,
GENERATESERIES(1,5)
)
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@JeroenN , Test as
Crossjoin(all(Tbl_Rent[Unit Id]), generateseries(1,5))
Thanks! Works perfectly.
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |