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
jjzhang
Regular Visitor

creating table using loop

Hi

I'm completely new to DAX and power BI, so I'm struggling with even the simple stuff.  I'm looking to create a new table based on distinct value from another table. For each of the distinct values, I want to create new rows with a list of variables. The end result looks like this:

Budget (distinct value from another table)

500

1000

1500

...

Year

2019

2020

2021

...

Result:

Budget     Year

500     2019

500     2020
500      2021

1000      2019

1000      2020
1000      2021
1500      2019

1500      2020
1500      2021

I am using a very primitive and manual way to generate my new table, Can anyway direct me as to a better way to do this (probably using some sort of for loop or generate list method)

let
#"BudgetList" = Table.FromList(List.Distinct(#"Budget Analysis"[Budget]), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"2020" = Table.AddColumn(BudgetList, "Year",each 2020),
#"2021" = Table.AddColumn(BudgetList, "Year",each 2021),
#"2022" = Table.AddColumn(BudgetList, "Year",each 2022),
#"2023" = Table.AddColumn(BudgetList, "Year",each 2023),
#"2024" = Table.AddColumn(BudgetList, "Year",each 2024),
CombinedYearlySummary = Table.Combine({
#"2020",
#"2021",
#"2022",
#"2023",
#"2024"})
in
#"CombinedYearlySummary"

2 REPLIES 2
v-juanli-msft
Community Support
Community Support

Hi @jjzhang 

Assume your budget table is as below

Capture2.JPG

Then create a new query names "year",

code in Advanced editor(create year from 2019~2024)

let
    Source = List.Numbers(2019,6),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "year"}}),
    Source2=Table.FromList(List.Distinct(#"budget table"[budget]), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each Source2),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1"}, {"Custom.Column1"})
in
    #"Expanded Custom"

Capture3.JPG

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
edhans
Super User
Super User

No magic required here. Just a little trick to make it a Cartesian Join.

 

I used Excel for this to make it easy.

  1. Go to your Year query and add a custom column.
  2. In the box, just type = Budget (or whatever the query your Budget data is coming from. If it has a space in it, it will be #"Budget Data")
  3. Expand the Table/column you joined
  4. Change the data type of budget, as that gets lost.

 

Full M code for the Year query which is were I added the "budget" column.

let
    Source = Excel.CurrentWorkbook(){[Name="Year"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Budget Table", each Budget),
    #"Expanded Budget Table" = Table.ExpandTableColumn(#"Added Custom", "Budget Table", {"Budget"}, {"Budget"})
in
    #"Expanded Budget Table"

 

Here is my Excel file if you want to look at it.

20191029 18_30_08-Year - Power Query Editor.png



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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.

Top Solution Authors
Top Kudoed Authors