how to fetch the values from the other table even there is no relation between two tables

[fab196]

Hi,

I have two tables nemely the data1 and data2

data1:                                                             

id	sales	list
1	23	23
2	22	530
3	66	224
4	55	550
5	20	550

data2:

category	start	stop
0-180	0	180
181-600	181	600

and result i wanted using powwer bi m query language(power query editor) is:

category	start	stop	list
0-180	0	180	23
181-600	181	600	224
181-600	181	600	530
181-600	181	600	550

the above table data1 and data2 have no relationship between two table.but based on the data i want to fecth the values from data1 table into the data2 table .

for ex. In my data in data1 table have list column contains multiple values .and i want fetch this values into the data2 table based category column .like in tbale data1 the first row has 23 value in the list field and in the data2 table its among the 0-180 so it will come to the 0-180 category same for the others .if there are duplicates in the data1 list column then distinct will returend.

please help

@amitchandak 

@parry2k 

[Greg_Deckler]

@fab196 Easiest way would be to create a custom column in Table 1 using multiple if then else statements to get the same thing as your Category column in Table 2. Then you could just do a Merge query.

So like:

if [list] < 181 then "0-180"
else if [list] < 600 then "181-600"
else "something else"

[fab196]

hi,

thnaks for your promp reply but i have found the result using list function but in list if one of the value of column is null then its showing error
like in below data:

start	end	list
0	20	list
21	30	list
31		error

the result i wanted is that : for  ex supose start and stop is 0-20 then list fuction giving me a reange of values between 1 to 20 but in the third row of data the end column have one null value .eg for 31-null due to that it is  throwing an error so for that how to i get the list of range between greater than 31 and so on

[v-easonf-msft]

Hi, @fab196 

Not clear.

Please show the current formula for the custom column you are using or a screenshot of it for further research.

Best Regards,
Community Support Team _ Eason

[fab196]

@Greg_Deckler please reply on above comment