Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi I am looking to do the following but I don't know how to go about doing it. I know how to do it via MS access but it's difficult to see how it'll work in Power BI
This is what I would like to do, with these 4 tables. Table 1 i would create a new table with two fields Timestamp & Stage. Table 2 I would append to the new table.
Table 1
TS_1:24/09/2016
Table 2
TS_2: 24/09/2016
Table 3
TS_3: 24/09/2016
Table 4
TS_4: 24/09/2016
Result
I would like to create a new table. Taking those columns from the 4 tables merging them into one column and putting some sort of text field it came from those fields "TS_1, TS_2, TS_3 & TS_4"
NEW TABLE
TimeStamp Field 2
24/09/2016 TS_1
24/09/2016 TS_2
24/09/2016 TS_3
24/09/2016 TS_4
Solved! Go to Solution.
Hi @anguyen83,
You can simply use below formula merge these tables.
New Table= UNION(SELECTCOLUMNS(Table1,"Stage","TS1","Date",[TS1]),SELECTCOLUMNS(Table2,"Stage","TS2","Date",[TS2]),SELECTCOLUMNS(Table3,"Stage","TS3","Date",[TS3]),SELECTCOLUMNS(Table4,"Stage","TS4","Date",[TS4]))
Regards,
Xiaoxin Sheng
Hi, How can we combine two columns from different tables with different number of columns and create a new table with a merged column??
Hi Team,
Currently I have used queries to get average values of each PR stages and used them in clustered column chart as shown below.
I have a requirement from our stakeholder that bars in column chart should be visible separately similarly like visual below.
I think it is possible only if we can create a table like this within Power BI, please help me understand to create a table like this.
Thanks and Regards,
Kranthi Kumar
Assuming that you have 4 different tables with the structure outlined in below screenshot.
You should use Append Queries Option in the Query Editor: See the attached screenshot.
and once this is done, Split the column by ":" delimiter. See the screenshot.
This will give the expected results outlined below.
Hi Bhavesh
Thank you for your response.
Really do appreciate you reaching out to me because I'm struggling to with this software as I been using MS ACCESS all my life
I have below is an example. There are 4 tables and each table has one column with dates and the field name TS1-4
What I need to do is create a new table, and take the field names of each of the table and create it's own column corresponding to the contents in each table.
So at the end I will get a what is in New table
In MS access I would do a make table query taking the data from TS1 and then an append query using the remaing tables
How does it work for Power BI?
Hi @anguyen83,
You can simply use below formula merge these tables.
New Table= UNION(SELECTCOLUMNS(Table1,"Stage","TS1","Date",[TS1]),SELECTCOLUMNS(Table2,"Stage","TS2","Date",[TS2]),SELECTCOLUMNS(Table3,"Stage","TS3","Date",[TS3]),SELECTCOLUMNS(Table4,"Stage","TS4","Date",[TS4]))
Regards,
Xiaoxin Sheng
Hi Xiaoxin
Thank you for your help. The first two selectcolumns worked pulling the data from the table called 'lead' however when I add in the remaing selectcolumns getting the data from a table called 'Contacts' it doesn't seem to work
Is there something wrong with my query? I'm not familar with DAX
It's halfway their
Thank you
Hi @anguyen83,
The first parameter of selectcolumns function is table type.(based on your screenshot, I find you input a column)
Regards,
Xiaoxin Sheng
Hi
I fixed it thank you
LS_Consolidated = Union(SELECTCOLUMNS('Lead',"Stage","TS1","Timestamp",[LS1_Timestamp__c].[Date],"Account_Id",[Id]),SELECTCOLUMNS('Lead',"Stage","TS2","Timestamp",[LS2_Timestamp__c].[Date],"Account_Id",[Id]),SELECTCOLUMNS('Contact',"Stage","TS3","Timestamp",[LS3_Timestamp__c].[Date],"Account_Id",[Id]),SELECTCOLUMNS('Contact',"Stage","TS4","Timestamp",[LS4_Timestamp__c].[Date],"Account_Id",[Id]))
So now the records I brought in there is Null under the timestamp field. I was wondering in this code is their a way to filter <> Null before the table is create or before you append the data?
Hi @anguyen83,
>>So now the records I brought in there is Null under the timestamp field. I was wondering in this code is their a way to filter <> Null before the table is create or before you append the data?
You can use filter function to filter the blank value, for example:
LS_Consolidated = Filter( Union(SELECTCOLUMNS('Lead',"Stage","TS1","Timestamp",[LS1_Timestamp__c].[Date],"Account_Id",[Id]),SELECTCOLUMNS('Lead',"Stage","TS2","Timestamp",[LS2_Timestamp__c].[Date],"Account_Id",[Id]),SELECTCOLUMNS('Contact',"Stage","TS3","Timestamp",[LS3_Timestamp__c].[Date],"Account_Id",[Id]),SELECTCOLUMNS('Contact',"Stage","TS4","Timestamp",[LS4_Timestamp__c].[Date],"Account_Id",[Id])),[Timestamp]<>blank())
Regards,
Xiaoxin Sheng
Use
Table = Union(Table1, Table2)
Hi Xiaoxin
Not sure what you mean by that?
Would you be able to provide an example? sorry the language of DAX is new to me.
Follow this steps:
1. Go to Edit Query
2. Select Append Queries as New
3. Select all your tables to Append
4. You have these results
5. Almost Close. Now Select your 4 columns TS1...TS4. and Unpivot Columns
6. You have this:
7. Finally: Change the name of both Columns : Stage , Date
8. Close & Apply
9. Ready
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |