Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Impactful Individual
Impactful Individual

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
Impactful Individual
Impactful Individual

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 III
Resolver III

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors