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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

Dynamic name extraction.

Hello All, 

I have around 43 tables in power bi desktop which I want to append into one table. But I also want to add a column that has the Table names in it corresponding to each row.

I did see many solutions regarding this on community but in all those i have to enter the table name manually, which I want to avoid at all costs since there are so many tables.

Below is the Output I want

Capture.PNG

I need a dynamic solution for this. Is it possible?

Thanks in advance

Chahat

 

1 ACCEPTED SOLUTION

Hi @Anonymous 

 

Profiling is easy you can reference this table and add extra column like below.
image.png

 

The transformation could be done in a similar way however I would advise creating a function for transformations.

 

Another option would be doing all the transformation in dataflows.

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski

 

View solution in original post

12 REPLIES 12
Mariusz
Community Champion
Community Champion

Hi @Anonymous 

Cool, I think there might be a better way.

In Query Editor.

  1. Create a Blank Query and Past the below, into the Advanced Editor 
    let
        Source = PowerBI.Dataflows([])
    in
        Source
    this will create a table with all workspaces that are available to you.
  2. Find the workspaceName and click on [Data] Column > "Folder", this will show you a list of all the dataflows within that workspace.
  3. Find your workspace and click on [Data] Column > "Database", this will the list of all the tables within this dataflow.
  4. Now, select entity ( your table names ) column and Data Column and Right Click > Remove Other Columns
  5. you should have a table like below.
    image.png 
    All you need to do now is expanding your tables by clicking on the highlighted arrows and selecting the columns to expand.

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski


 

Anonymous
Not applicable

@Mariusz  actually after we get tables from the dataflow we are doing a data profiling transformations on all of them so that we can get information about their columns and than appending them.  How will I do this tranformations then.

 

Table.Profile(Table 1)

Hi @Anonymous 

 

Profiling is easy you can reference this table and add extra column like below.
image.png

 

The transformation could be done in a similar way however I would advise creating a function for transformations.

 

Another option would be doing all the transformation in dataflows.

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski

 

Anonymous
Not applicable

@Mariusz  Yes. I did try that. Still the expand step is taking like forever. I dont know whats wrong with it.

ThankYou very much though. I appreciate your helpSmiley Happy.

Regards,

Chahat

@Anonymous  I had a similar issue, the easiest thing to do is to load all the tables to SQL Server and then combine them and adding a simple query to get the table names appended and then feed to Power BI.

Mariusz
Community Champion
Community Champion

Hi @Anonymous 

 

Try something like this.

let
    Source = #shared,
    #"Converted to Table" = Record.ToTable(Source),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each  Type.Is( Value.Type( [Value] ), type table)  ),
    #"Filtered Rows1" = Table.SelectRows(#"Added Custom", each not ([Name] = "Query1")),
    #"Filtered Rows" = Table.SelectRows(#"Filtered Rows1", each ([Custom] = true))
in
    #"Filtered Rows"

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski

 

Anonymous
Not applicable

@Mariusz thankyou for the solution. It does seem to work but when I expand the value column, it gets stuck after loading around just 190 rows and I have like 45 Tables and stays that way foreverSmiley Sad. Also I want to remove all the tables after appending them. Just so there is only one main table. 

Hi @Anonymous 

 

Where do these tables come from?

 

Many Thanks

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski

 

Anonymous
Not applicable

@Mariusz PowerBI dataflows.

HI @Anonymous 

 

Is it all the same workspace and the same dataflow?

 

Many Thanks

Mariusz

Anonymous
Not applicable

@Mariusz Yes. All the tables are in one workspace and one dataflow of that workspace.

Greg_Deckler
Super User
Super User

What are these tables sources and I assume that these are all individual queries?



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.