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

insert specific rows from a table into a second table with condition

Hello,

 

I have 2 excel file : one with the detail of every piece, and one with the detail of every machine/operator.

vadim_g_0-1656680926841.pngand vadim_g_1-1656680926842.png

 

I want to copy on first excel (piece) specific rows of the second excel (machine) only if the rows is "ok". The specific rows of the second excel is choosed like that : the value of the piece of the first excel must be contained in the range value of second excel. Sorry for my english, better with the result that i want :

vadim_g_3-1656681494762.png

 

Thanks a lot

1 ACCEPTED SOLUTION
KT_Bsmart2gethe
Super User
Super User

Hi @vadim_g ,

 

The ideal way to have your challenge resolve in a timely and accurate manner is by providing data in table format with all sensitive information removed.

 

Please see below for proposed solution.

 

Table1: The detail of every piece

Table2: The detail of every machine/operator

 

Below is a direct query and extract data from Table1 to Table2:

 

1. Add a custom step and add formula below (i.e. the direct query method):

if [#"ok/nok"] = "ok" then Table.SelectRows(Table2,(x)=> ([piece] >= x[begin piece] and [piece] <= x[end piece])) else null

KT_Bsmart2gethe_5-1656724493540.png

 

 

 

2. Expand the column:

KT_Bsmart2gethe_4-1656724469771.png

 

 

Result:

KT_Bsmart2gethe_6-1656724547239.png

 

Regards

KT

 

View solution in original post

2 REPLIES 2
KT_Bsmart2gethe
Super User
Super User

Hi @vadim_g ,

 

The ideal way to have your challenge resolve in a timely and accurate manner is by providing data in table format with all sensitive information removed.

 

Please see below for proposed solution.

 

Table1: The detail of every piece

Table2: The detail of every machine/operator

 

Below is a direct query and extract data from Table1 to Table2:

 

1. Add a custom step and add formula below (i.e. the direct query method):

if [#"ok/nok"] = "ok" then Table.SelectRows(Table2,(x)=> ([piece] >= x[begin piece] and [piece] <= x[end piece])) else null

KT_Bsmart2gethe_5-1656724493540.png

 

 

 

2. Expand the column:

KT_Bsmart2gethe_4-1656724469771.png

 

 

Result:

KT_Bsmart2gethe_6-1656724547239.png

 

Regards

KT

 

PurpleGate
Resolver II
Resolver II

Hi,

 

 

1. On table 2 (Operator) Add a custom column to see all the pieces between the "begin piece" and the "end piece"

 

{Number.From([begin piece])..Number.From([end piece])}

 

PurpleGate_1-1656686559895.png

 

2. expand list

PurpleGate_2-1656686573841.png

 

3. Created a duplicate table 1 (ok/nok) 

4. Filtered to see only "ok"

PurpleGate_3-1656686588749.png

 

5. Merge Table 2 (custom column) onto Table 1 (Piece)

6. Expand table

PurpleGate_4-1656686606762.png

PurpleGate_5-1656686638182.png

 

 

7. Merge Duplicated Table1 onto Original Table1

 

PurpleGate_6-1656686677060.png

 

8. Expand table to include only the columns you want

 

End Result:

PurpleGate_7-1656686778590.png

 

 

 

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Kudoed Authors