Forum Discussion
Helper IICreate a table with repeated labels
Hi everybody,
I'm trying to create a new table with repeated labels.
Table A is a table with dates, Table B is a table with the business units.
I want to create a table which repeats the date values for n number of business units as shown in table C.
Table A:
| Date |
| 2020-1-1 |
| 2020-1-2 |
| 2020-1-3 |
2020-1-4 |
2020-1-5 |
Table B:
| BusinessUnit |
| BusinessUnitA |
| BusinessUnitB |
| BusinessUnitC |
| BusinessUnitD |
| BusinessUnitE |
Table C:
| Date | BusinessUnit |
| 2020-1-1 | BusinessUnitA |
| 2020-1-1 | BusinessUnitB |
| 2020-1-1 | BusinessUnitC |
| 2020-1-1 | BusinessUnitD |
| 2020-1-1 | BusinessUnitE |
| 2020-1-2 | BusinessUnitA |
| 2020-1-2 | BusinessUnitB |
| 2020-1-2 | BusinessUnitC |
| 2020-1-2 | BusinessUnitD |
| 2020-1-2 | BusinessUnitE |
| 2020-1-3 | BusinessUnitA |
| 2020-1-3 | BusinessUnitB |
| 2020-1-3 | BusinessUnitC |
| 2020-1-3 | BusinessUnitD |
| 2020-1-3 | BusinessUnitE |
Does somebody know which formula I could use to create this table? Or is this actually possible?
OK, you have two choices:
1. Create the date table in Power Query and Add the column as in the first post.
Or
2. Create the new table in DAX which would be CROSSJOIN (Table A, Table B)
4 Replies
- HotChilli
Community Champion
In Power Query, Add a custom column to the date table(Table A).
The custom column should be the name of the other table (Table B - the intellisense will complete it once you start typing)
jhaastThe date table A is created after the Power Query menu, with the new table function and the following formula:
Dates = CALENDAR (DATE(2003;01;1); DATE(YEAR(TODAY())+1;12;31))The Business Unit table B is imported from a SQL database with Power Query.- HotChilli
OK, you have two choices:
1. Create the date table in Power Query and Add the column as in the first post.
Or
2. Create the new table in DAX which would be CROSSJOIN (Table A, Table B)
You can simply add a column with this formula:
=Text.Repeat("a",[Column5])
In column 5 should be the count you want for rows to be duplicated.
Then, from transform tab, split the new column by number of characters, use 1 char split, but from advanced settings, make sure you split into rows, not into columns, this will duplicate all other columns.
Now you can remove the additional column, to diplay only the results.
I can be done with custom M code, with List.Accumulate, but it's easier via interface, see the attached file.
