cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NIB123
Frequent Visitor

Add rows containing a list of IDs for each categories

Hello to the community,

Currently, I'm stuck with this data transformation in power query editor :

I have one table named "seasonality" with all the dates of the years :

NIB123_0-1626946981551.png

 

I have a second table  named "Data Equipment" with several ID :

NIB123_1-1626947108203.png

 

And I want combine this tables with this transformation : for each dates, add all rows with this IDs  

Desired result 

NIB123_0-1626954431062.png

 


If someone can help me.. or offers me another solution of transformation, group by etc..

Thank you in advance !



1 ACCEPTED SOLUTION
HotChilli
Super User II
Super User II

2 things:

1 - to get more detail on errors, click in the error box (not on the error) and you'll get a further details below the dataset.

2 - did you write that code yourself because there's a lot wrong with it.  I suggest deleting the step.

After that, click the 'custom column' on the Add Column menu.  In the formula box put the name of the other table (if you write # , intellisense should offer you the name). Click OK.

If that works, expand the column header to return the correct field.

View solution in original post

7 REPLIES 7
HotChilli
Super User II
Super User II

2 things:

1 - to get more detail on errors, click in the error box (not on the error) and you'll get a further details below the dataset.

2 - did you write that code yourself because there's a lot wrong with it.  I suggest deleting the step.

After that, click the 'custom column' on the Add Column menu.  In the formula box put the name of the other table (if you write # , intellisense should offer you the name). Click OK.

If that works, expand the column header to return the correct field.

View solution in original post

NIB123
Frequent Visitor

Thanks a lot ! It's working ! 😁

I'm beginner with Power BI so I didn't know this trick..

HotChilli
Super User II
Super User II

I can help if you tell me what you've got so far.   Any errors?  

Here is my error

NIB123_0-1626964152619.png

 

NIB123
Frequent Visitor

Here is my error

NIB123_0-1626964152619.png

 

HotChilli
Super User II
Super User II

In seasonality, add custom column and just put the name of the 2nd table.  You can then expand the header.

 

If the 2nd table has many colmuns but you just want to add one then just put the Table[columnName] in the Add custom column code box.  You will then be able to expand the list to new rows

NIB123
Frequent Visitor

This solution not working or may be I didn't understand

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors