Create multiple rows of the same data

[Craigc3814]

I have two tables that I want to join, no data from either matches and this is intentional.

The first table is a bunch of data that includes the hours charged to a given project # sometimes you do not charge time to a specific project you are just doing admin work. I have to split that admin work over all of the available project numbers you could charge time to.

The second table is one column and it is a list of 16 project numbers available to charge time to.

I want to join the first table to the second table and for each row in the first table I want to create 16 lines of that same data one line for each project number from the second table. I thought I could do an anti join but no luck.

 

[Craigc3814]

As usual 10 minutes after posting I figured it out. Here is my solution in case anyone runs into it in the future.

On table one I changed the project numbers from a blank to a non existent project number

On table two I created an extra column and made 16 rows of the non existent project number, one for each actual project number and titled the column merge


I then did a regular merge from table two to table one on the non existent project numbers. It duplicated the rows sixteen times, one for each actual project number. I deleted the merge column and I am set.

 

[sandy666]

maybe shorter would be add custom column to the first table with =Table2 ?

 

[horseyride]

Not quite sure what you are doing, but the simplest way to duplicate rows 16 times is add a custom column with formula of ={1..16} then expand it to new rows

#"Added Custom" = Table.AddColumn(PreviousStep, "Custom", each {1..16}),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")

then if I wanted to merge in ColumnA from Table2, I'd add an index to Table1 with name Index, then add 1 second custom column with formula <code>=Table2[ColumnA]{[Index]}</code>

 

[Craigc3814]

I wanted to do it this way because the amount of project numbers can change, tomorrow a new project number could be added and there would now be 17 projects. I believe using a formula I would have to go in and account for that each time, no? This way if a new project pops up I can add it to table 2 and it is accounted for on refresh only.

 

[sandy666]

I wanted to do it this way because the amount of project numbers can change, tomorrow a new project number could be added and there would now be 17 projects. I believe using a formula I would have to go in and account for that each time, no? This way if a new project pops up I can add it to table 2 and it is accounted for on refresh only.

so use post #3
you can add whatever to source1 or/and source2 then refresh

test it first