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.
Hi,
I am new Power BI and am really struggling to create a column
I have 2 tables, 1 that contains stripped down IP address columns and another table that contains an IP range and a location name.
Table 1 = column1 (192.168) column2 (123)
Table 2 = colunm1 (192.168) column2 (100) column3 (150) column4 (location name)
I need to pull the location name into table1
In its simplest form I believe I can express the query as follows:
if table1 column 1 = table2 column 1 and table1 column2 >= table2 column2 and <= table2 column3 return table2 column4
Any help or insight would be greatly appreciated
Alan
Solved! Go to Solution.
Hi @Baldy,
Based on my test with your sample data, you should be able to use the formula(DAX) below to create new calculate column in Table1 in your scenario.
Column3 = CALCULATE ( FIRSTNONBLANK ( Table2[Column4], 1 ), FILTER ( Table2, Table2[Column1] = Table1[Column1] && ( Table1[Column2] >= Table2[Column2] && Table1[Column2] <= Table2[Column3] ) ) )
Regards
Hi @Baldy,
Based on my test with your sample data, you should be able to use the formula(DAX) below to create new calculate column in Table1 in your scenario.
Column3 = CALCULATE ( FIRSTNONBLANK ( Table2[Column4], 1 ), FILTER ( Table2, Table2[Column1] = Table1[Column1] && ( Table1[Column2] >= Table2[Column2] && Table1[Column2] <= Table2[Column3] ) ) )
Regards
This works perfectly.
Thank you to every one for your time it is very much apritiated
Alan
Unless I don't understand it, use your query for this.
select t1.*, t2.* from table1 t1 left join table2 t2 on t2.col1=t1.col1 and t1.col2>=t2.col2 and t1.col2<=t2.col3
Then your joining table2 on table1, with the criteria that col1 of both are equal, and col2 of table1 is equal or greater then table2.col2 and equal or smaller then table2.col3.
The select part you can alter to prevent extra data in for example,
select t1.col1, t1.col2, t2.col4 from table1
This wil result in
192.168 ; 126 ; {location}
This would have described my question a bit better
Table 1
Column1 | Column2 |
192.168. | 123 |
Table 2
Column1 | Column2 | Column3 | Column4 |
192.168. | 100 | 150 | location1 |
192.168. | 151 | 200 | location2 |
192.168. | 201 | 250 | location3 |
The result should be
Table 1
Column1 | Column2 | Column3 |
192.168. | 123 | location1 |
192.168. | 224 | location3 |
From what little I know about sql I believe your query is correct
However I dont understand t1.*, t2.* from table1 t1. I get the * means "all" but the t1,t2 I dont understand where this comes from or where to run your whery.
Sorry for being such a noob 😞
I also need the data pulled into in the original table as a new column
Alan
No problem.
I'll type it with some more additional info.
select t1.*, t2.* from table1 t1 left join table2 t2 on t2.col1=t1.col1 and t1.col2>=t2.col2 and t1.col2<=t2.col3
As a small explanation:
select * from table1
that's your starting base.
When you're going to join tables, you have to specify which columns from which tables your referring to.
To make your typwork a bit smaller you can alter this to
select * from table1 as t1
(you can make it even smaller with ditching the 'as')
so you would get
Select * FROM table1 t1
now you're going to join the second table with
left join table2 t2
Here again, i'm adding the t2 to prevent me to have to type the table2 all the time.
so you would get
select * from table1 t1 Left join table2 t2
To know on what to join, jou add the columns which correspondent to each other.
left join table2 t2 on t1.col1=t2col1 and t1.col>=t2.col2 and t1.col2<=t2.col3
here the t1 and t2 come in handy, if you don't want to use it you would get this more to type query which does exactly the same
left join table2 on table1.col1=table2.col1 AND table1.col2>=table2.col2 .....
Then you can select what values you want to show by altering the select * from statement to
SELECT t1.col1, t1.col2, t2.col4 FROM Table1 t1 LEFT JOIN table2 t2 on t1.col1=t2col1 AND t1.col2>=t2.col2 AND t1.col2<=t2.col3
if you use t1.*,t2.* all values are taken so you would get the same as when in the above query you type
SELECT t1.col1, t1.col2,t2.col1,t2.col2,t2.col3,t2.col4 From Table1
so your query should be:
SELECT t1.col1, t1.col2, t2.col4 FROM Table1 t1 LEFT JOIN table2 t2 ON t1.col1=t2col1 AND t1.col2>=t2.col2 AND t1.col2<=t2.col3
This query creates a table in Power BI generates a table which contains your data.
Hopefully I made it more clear this way in a for beginners understanding way.
(EDIT: put the codes in code-blocks
Sorry 😞
(EDIT: put the codes in code-blocks?
I tried to ut it in a new table, not sure if thats what im supposed to do. Table 2 = select t1.*, t2.* from table1 t1 left join table2 t2 on t2.col1=t1.col1 and t1.col2>=t2.col2 and t1.col2<=t2.col3
It didnt work. red wiggly underscores prety much all the way 😞
Sorry to be a pain in the a......
I'm assuming you have a connection to a database where you're retrieving the information from?
If so, edit your Query (or create a new one).
This creates a new table with the three fields (col1, col2, col4)
step 1
step 2
step 3
Sorry no,
The sources are 2 separate spreadsheets that are made available to me via sharepoint.
Hi
You can join those tables in relationship model with matching column and in the report, you can drag and drop whatever the columns you want from both the tables.
Thank you for the quick reply.
I cannont use relationships because there are many values that are the same.
We operate a very large class B network. 192.168 appears many times in column1 on both sheets
Hi
Another quick approach is, you can duplicate the table and can keep only column1 and column 4 (location) then you can remove the duplicates and consider that table (query) for your relationship model.
Thanks
Hari
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |