Forum Discussion

Applicable88's avatar
Applicable88
Impactful Individual
3 years ago
Solved

Create more rows in calendar table

Hello,

 

how can I create new rows in power query. A join and a union is not whats working here, since there is no match on both sides.

 

I have a calendar:

Date Month
08.11.2022 11
09.11.2022 11
10.11.2022 11
11.11.2022 11
12.11.2022 11
13.11.2022 11
14.11.2022 11
15.11.2022 11
16.11.2022 11

 

And I need the categorie a,b and c for every date:

Date Month Category
08.11.2022 11 a
08.11.2022 11 b
08.11.2022 11 c
09.11.2022 11 a
09.11.2022 11 b
09.11.2022 11 c

and so fourth...

Hope someone know if there is any functionality within Powerquery for it.

Thank you very much in advance.

Best.

 

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    3 years ago

    Do a full out join between the tables using the Merge Function in Power Query.  They will need to be of the same data type, so change the date first to Text then merge then change date back to Date type.

     

    You could create a field in both tables that have the same value and then merge on that field, e.g. TableA[Key] = TableB[Key] where Key is the value "ABC" in both.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Do a full out join between the tables using the Merge Function in Power Query.  They will need to be of the same data type, so change the date first to Text then merge then change date back to Date type.

     

    You could create a field in both tables that have the same value and then merge on that field, e.g. TableA[Key] = TableB[Key] where Key is the value "ABC" in both.