Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |