Create a table with repeated labels

[jhaast]

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?

[Anonymous]

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.

[HotChilli]

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)

[jhaast]

The 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 · Accepted solution]

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)