cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

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

@JeroenN , Test as

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

5 REPLIES 5
Super User III
Super User III

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!


Super User III
Super User III

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

Super User IV
Super User IV

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

Proud to be a Super User!

Website   YouTube    LinkedIn
Super User IV
Super User IV

@JeroenN , Test as

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Thanks! Works perfectly.

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors