Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello
If I load a table from an excel file to PowerBI, can I create a new column in the table and populate it with values that equal to the name of the loaded table?
In other words, can I use the name of a table as a variable or value to put in fields?
Thanks!
Hi, @UsePowerBI
If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.
Best Regards
Allan
Hi, @UsePowerBI
Based on your description, you may click 'Edit Query', go to Query Editor, create a new blank query.
If you use Excel connector, you may input the following codes in the 'Advanced Editor'.
Then you need to input the Filename to get the result.
If you use folder connector, you may input the following codes in 'Advanced Editor'.
Here is the result.
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @UsePowerBI ,
If you are using Power Query you can go to to 2nd step and filter the files you wanna get, remove the other columns and expand the rows...so you will get a column with the sheet name.
Ricardo
Thanks but not sure if I was understood.
The xls I am loading is called Excel1.xls and its sheet is called Table1.
The table it contains is:
Header1 | Header2 | Header3 |
r | k | f |
o | b | r |
e | v | p |
I want to create a table like this:
Header1 | Header2 | Header3 | NewColumn1 | NewColumn2 |
r | k | f | Table1 | Excel1 |
o | b | r | Table1 | Excel1 |
e | v | p | Table1 | Excel1 |
How can I do that?
Thanks!
@UsePowerBI , Example of moving data from one table to another
New column in table1
New Column = maxx(filter(table2,table2[Col1]= table1[col1] && table2[Col2]= table1[col2] ),table2[required_col])
New column in Table 1 = maxx(filter(table2,table1[customer] = table2[customer] && table2[option]="construction",table2[value])
New column in Table 1 = maxx(filter(table2,table1[Attribute] = table2[name] && table1[project] = table2[project]),table2[name])
User | Count |
---|---|
98 | |
90 | |
83 | |
70 | |
67 |
User | Count |
---|---|
113 | |
104 | |
101 | |
73 | |
65 |