Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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
I need a dynamic solution for this. Is it possible?
Thanks in advance
Chahat
Solved! Go to Solution.
Hi @Anonymous
Profiling is easy you can reference this table and add extra column like below.
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.
Hi @Anonymous
Cool, I think there might be a better way.
In Query Editor.
let Source = PowerBI.Dataflows([]) in Sourcethis will create a table with all workspaces that are available to you.
@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.
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.
@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 help.
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.
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"
@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 forever. Also I want to remove all the tables after appending them. Just so there is only one main table.
HI @Anonymous
Is it all the same workspace and the same dataflow?
Many Thanks
Mariusz
@Mariusz Yes. All the tables are in one workspace and one dataflow of that workspace.
What are these tables sources and I assume that these are all individual queries?
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
User | Count |
---|---|
94 | |
90 | |
83 | |
76 | |
49 |
User | Count |
---|---|
145 | |
141 | |
109 | |
69 | |
55 |