cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JeroenN
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
amitchandak
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
PhilipTreacy
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!


AlB
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

Fowmy
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
amitchandak
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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

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.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.