creating table using loop

[Anonymous]

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"

[v-juanli-msft]

Hi @Anonymous 

Assume your budget table is as below

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"

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]

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.