Merge two tables, and update column header with data in specific column

[Naushad123]

Hi all,

i have two tables

Table 1 is about the allowances assign to specific designation

table2

Table 2 in emplyee payroll table

i want to create a column with specific allowances and amount having allowance name header so that if in future any employee gets difference allowance i can only update my Table 1 and after creation of column in Table 2 the amount goes for the designation wise employees.

[Anonymous]

Hi,

You can use append queries on the basis of EmpID.

[Naushad123]

Not working.

[Anonymous]

Hi @Naushad123,

I think what you are after is the join/merge on the Designation field. Then expand Allowance types. Then pivot them.

The trick may be with pivoting if you want to make it dynamic on the allowance types included in the final dataset. If you need this let me know I will provide a solution -or you can do it yourself - this is an intersection of Table.ColumnNames on Table 2 and List.Distinct on Designation field in Table 1.

Kind regards,
JB

[dax]

Hi @Naushad123 , 

I am not clear about your requirement, it seems that you want to update  amount in table 2, right? I want to know which amount did you want to show for "Managing Director"?

 

If possible, could you please inform me more detailed information(such as your expected output and your sample data )? Then I will help you more correctly.

Please do mask sensitive data before uploading(you could typing the data or upload by OneDrive for Business ).

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Naushad123]

Thanks @dax, yes it is like that.i am sorry i double the table2, the first table1 with name desigallow, i want to update the amount specificaly for managing director like you said but in "Orderly Allow" column only, likewise Naib Qasid "integrated Allow" column have value of 450.

Table 1 Allowances record become column in Table2, and update with the amount mentioned in Table1, the criteria in Table 2 is Designation Column,

i hope i tried my best to make you understand.

[ImkeF]

Hi it's not clear to me what you'r after. Please check this article on how to improve qualilty of your question: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

You've posted 2 (1.5) Tables with the name Table2, that doesn't help understanding.