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 all
Hopefully, someone can help me to solve the problem as I tried so many times but did not succeed.
Basically, what I want is to fill the missing values in the following columns of
Table 1:
art.nr |
art.id |
art.name |
by looking up the values from Table 2 and three.
Table 1 and Table 2 have the same values in each raw:
segment id |
market coverage % need |
market coverage % total |
Table 1 and Table 3 have the same values in each raw:
country |
region |
sub-region |
Table 2 and Table 3 have the same values in each raw:
art.nr |
art.id |
art.nr and art.id are unique for each raw
segment id, country, region, sub-region, market coverage % need and market coverage % total are not unique for each raw
but in combination (all together) should give a unique value
Is there a way I can do some kind of lookup and fill the missing values in Table 1 from table 2 and Table 3 based on the data with same values from columns in combination, so I have the complete data?
Help would be greatly appreciated.
Solved! Go to Solution.
[market coverage % need] and [market coverage % total] is typically not columns one would use for joining.
Looking at your data it seems like there is no unique combination of [market coverage % need], [market coverage % total], [segment id], [country], [region] and [sub region]. But not quite sure, hard to read from from your screen shots(usually easier for anyone to help you if you share a sample report or sample datasett instead of screen shots).
Anyway, if the combinations of the mentioned columns are unique, you can do the following:
1. create new columns in table 3:
Segment ID =
LOOKUPVALUE (
'Table 2'[segment ID],
'Table 2'[art.nr], 'Table 3'[art.nr],
'Table 2'[art.id], 'Table 3'[art.id]
)
Segment ID =
LOOKUPVALUE (
'Table 2'[market coverage % need],
'Table 2'[art.nr], 'Table 3'[art.nr],
'Table 2'[art.id], 'Table 3'[art.id]
)
Segment ID =
LOOKUPVALUE (
'Table 2'[market coverage % total],
'Table 2'[art.nr], 'Table 3'[art.nr],
'Table 2'[art.id], 'Table 3'[art.id]
)
2. In table 1 create the column [art.nr] like this
art.id =
LOOKUPVALUE (
'Table 3'[art.id],
'Table 3'[segment id], 'Table 1'[segment id],
'Table 3'[country], 'Table 1'[country],
'Table 3'[region], 'Table 1'[region],
'Table 3'[sub-region], 'Table 1'[sub-region],
'Table 3'[market coverage % total], 'Table 1'[market coverage % total],
'Table 3'[market coverage % need], 'Table 1'[market coverage % need],
)
If the combinations of the columns are not unique, you will get an error message
Cheers,
Sturla
If this post helps, then please consider Accepting it as the solution. Kudos are nice too.
[market coverage % need] and [market coverage % total] is typically not columns one would use for joining.
Looking at your data it seems like there is no unique combination of [market coverage % need], [market coverage % total], [segment id], [country], [region] and [sub region]. But not quite sure, hard to read from from your screen shots(usually easier for anyone to help you if you share a sample report or sample datasett instead of screen shots).
Anyway, if the combinations of the mentioned columns are unique, you can do the following:
1. create new columns in table 3:
Segment ID =
LOOKUPVALUE (
'Table 2'[segment ID],
'Table 2'[art.nr], 'Table 3'[art.nr],
'Table 2'[art.id], 'Table 3'[art.id]
)
Segment ID =
LOOKUPVALUE (
'Table 2'[market coverage % need],
'Table 2'[art.nr], 'Table 3'[art.nr],
'Table 2'[art.id], 'Table 3'[art.id]
)
Segment ID =
LOOKUPVALUE (
'Table 2'[market coverage % total],
'Table 2'[art.nr], 'Table 3'[art.nr],
'Table 2'[art.id], 'Table 3'[art.id]
)
2. In table 1 create the column [art.nr] like this
art.id =
LOOKUPVALUE (
'Table 3'[art.id],
'Table 3'[segment id], 'Table 1'[segment id],
'Table 3'[country], 'Table 1'[country],
'Table 3'[region], 'Table 1'[region],
'Table 3'[sub-region], 'Table 1'[sub-region],
'Table 3'[market coverage % total], 'Table 1'[market coverage % total],
'Table 3'[market coverage % need], 'Table 1'[market coverage % need],
)
If the combinations of the columns are not unique, you will get an error message
Cheers,
Sturla
If this post helps, then please consider Accepting it as the solution. Kudos are nice too.
@DanielPasalic - Difficult to follow. Can you post sample source data as text and the results of that sample data that you expect?
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 |
---|---|
49 | |
26 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
43 | |
19 | |
18 |