Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JeroenN
Advocate I
Advocate I

Create new table with generated series

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 IdScenario
1001001   10010011
1001002   10010012
1001003   10010013
1001004   10010014
1001005   10010015
1001006   10010021
1001007   10010022
1001008   10010023
1001009   10010024
1001010   10010025
1001011   10010031
    10010032
    10010033
    10010034
    10010035
    10010041

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@JeroenN , Test as

Crossjoin(all(Tbl_Rent[Unit Id]), generateseries(1,5))

View solution in original post

5 REPLIES 5
PhilipTreacy
Super User
Super User

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 



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


AlB
Super User
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 

SU18_powerbi_badge

Fowmy
Super User
Super User

@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 🙂

YouTube  LinkedIn

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

amitchandak
Super User
Super User

@JeroenN , Test as

Crossjoin(all(Tbl_Rent[Unit Id]), generateseries(1,5))

Thanks! Works perfectly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.