Select Rows from specified column of Table for multiple rows of data tables

%3CLINGO-SUB%20id%3D%22lingo-sub-1426040%22%20slang%3D%22en-US%22%3ESelect%20Rows%20from%20specified%20column%20of%20Table%20for%20multiple%20rows%20of%20data%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1426040%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20to%20consolidate%20data%20from%20multiple%20files%20as%20shown%20below%20in%20Name%20Column.%20Custom%20column%20contains%20data%20in%20the%20tables.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-right%22%20image-alt%3D%22Screenshot%20(24).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F195301i0DEA68F81F119F4E%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Screenshot%20(24).png%22%20alt%3D%22Excel%20Query%20Screenshot%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EExcel%20Query%20Screenshot%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EOne%20of%20the%20Table%20preview%20is%20shown%20above%20and%20I%20want%20to%20filter%20data%20in%20Column1%20that%20matches%20the%20condition.%20Example%3A%20Select%20all%20rows%20with%20text%20%22Exempt%22%20or%20%22Particulars%22%20etc.%3C%2FP%3E%3CP%3EI%20used%20Table.SelectRows%20but%20following%20error%20is%20shown%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%20(26).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F195303iA07C74EF395BA91B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Screenshot%20(26).png%22%20alt%3D%22After%20query%20error%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EAfter%20query%20error%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20suggest%20what%20formula%20to%20use%20to%20get%20desired%20results.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1426040%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1426121%22%20slang%3D%22en-US%22%3ERe%3A%20Select%20Rows%20from%20specified%20column%20of%20Table%20for%20multiple%20rows%20of%20data%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1426121%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F86737%22%20target%3D%22_blank%22%3E%40Ravitosh%20Kumar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20add%20column%20as%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3E...%2C%20each%20%5BCustom%5D%5BColumn1%5D%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eexpand%20resulting%20lists%20to%20rows%20and%20filter%20as%20needed.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

I have to consolidate data from multiple files as shown below in Name Column. Custom column contains data in the tables.

Excel Query ScreenshotExcel Query Screenshot

One of the Table preview is shown above and I want to filter data in Column1 that matches the condition. Example: Select all rows with text "Exempt" or "Particulars" etc.

I used Table.SelectRows but following error is shown

 

After query errorAfter query error

 

Please suggest what formula to use to get desired results.

1 Reply
Highlighted

@Ravitosh Kumar 

You may add column as

..., each [Custom][Column1]

expand resulting lists to rows and filter as needed.