Extract Multiple Tables from a single query

Power Query
1

Hi friends,
i need some suggestions, actually i have a single query/table which has a field/column according to which i want to seperate and obtain seperate query for as per each field but dynamically. I know i can create a parameter and then create a function to invoke filtered tables. But i need a single M Code/or function which can automatically seperate tables for me instead of invoking function again and again. Please guide. I am sharing excel file for reference as source and i want to seperate tables by depttype column in power query in power bi.
thanks and regards
data.xlsx (8.7 KB)

*** READ FIRST ***

Before you send your question.
Make sure that all details relevant to your question is complete:

  • Your current work-in-progress PBIX file - VERY IMPORTANT
  • A clear explanation of the problem you are experiencing
  • A mockup of the results you want to achieve
  • Your underlying data file

Check out this thread on Tools and Techniques for Providing PBIX Files with Your Forum Questions

Also make sure that your data file contains no confidential information. If it does, click the link above.

*** DELETE THIS MESSAGE IF YOU ARE SURE ALL YOUR DETAILS ARE COMPLETE OR IF THE ABOVE INFORMATION IS NOT APPLICABLE TO YOUR QUESTION.***

2

Hi @praveer82,

AFAIK at this time there is no way to output several “queries” from one invocation. However you can partition your data automaticaly, after which you’ll have to drill down in each table once to expand them.

Here’s a custom function that will do just that.

( t as table, DeptTypeCol as text ) as list =>
let
    DistinctDeptType = List.Buffer( List.Distinct( Table.ToColumns( Table.SelectColumns(t, DeptTypeCol )){0}? )),
    TypeCount = List.Count( DistinctDeptType ),
    Output = Table.Partition(
        t,
        DeptTypeCol,
        TypeCount,
        (x)=> List.PositionOf( DistinctDeptType, x)
    )
in
    Output

It requires the name of the Source table and the name of the Column which contains the DeptType and will return a list that contains a table for each of the unique values in the DeptType column.

image

To expand these nested tables, right click off to the side in the white space beside them and choose: Add as new query.

You can also watch this YT video that demonstrates the Table.Partition function.

I hope this is helplful

2 Likes
3

thanks mam for reply…