Data modeling

[Polar_A]

Hi all,

I need some help with my data tables. Essentially I have 4 different tables from Smart sheets. I want to combine them into one but I am not sure how to join them. Essentially I would want to combine all of the Task Names together so I can see which people have been assigned which tasks and their progresses.

I would want to essentially create a table that has these headings:

Is it possible also to create a field in the table that states where the source data is from (grouping) or will I need to create a new column in the Power Query?

KR

Polar

[Polar_A]

Thank you very much this was the answer

[Anonymous]

Hi, @Polar_A 

I’d like to acknowledge the valuable input provided by the @Ritaf1983 . Their initial ideas were instrumental in guiding my approach. However, I've found that merging operations can also be done with UNION in DAX. I created the following two tables:

Table:

Table1:

I used the following DAX to merge the two tables:

Mergetable = UNION('Table','Table1')

The results of the merger are as follows:

In addition, you can also summarize the corresponding columns by using SUMMARIZE to get the header you want. Or put the corresponding column directly in the table visual:

Header = SUMMARIZE(Mergetable,Mergetable[State],'Mergetable'[Country/Region])

table visual:

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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

[Ritaf1983 · Accepted solution]

Hi @Polar_A 
You need to append these tables from PQ.
Please refer to the linked documentation:

https://learn.microsoft.com/en-us/power-query/append-queries

and video tutorial:
https://www.youtube.com/watch?v=urGtQ1baIKc

+ to make this process automatically change the data source from "Excel / CSV" to folder.

please refer to :

https://www.youtube.com/watch?v=Nbhd0B5ldJE

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