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.
Hello,
My problem is quite easy but I cannot find a solution. In Excel the green columns would do the the trick for the first part of the problem:
I have in Power Query (BI) the following as a table containing only the yellow columns. With Excel formulas I can quite easily fetch the helper columns (two custom columns + department type) and the result columns (team name, company name, area name). Results columns are the ones that I would like to have but I currently have only one column (row name) which contains department info that can be on different levels (team, company or area). In Excel 'custom columns' are fetched using index match from id & parent id columns. Department type fetched from 'row name' column (if row contains team, company or area).
How can I do the same in query?
The formulas for the above:
ID=K column, row name = L etc.
Formula | Formula | Formula | Formula | Formula | Formula | |||
id | parent id | row name | custom column: parent name | Custom column: parent name 2 | Department type | Team name | Company name | Area name |
1 | 2 | Team 1 | =INDEX(L:L;MATCH(K5;J:J;0)) | =INDEX(M:M;MATCH(M5;L:L;0)) | =IF(ISNUMBER(SEARCH("team";L5));"Team";IF(ISNUMBER(SEARCH("company";L5));"Company";"Area")) | =IF(O5="Team";L5;"") | =IF(O5="Team";M5;IF(O5="company";L5;"")) | =IF(O5="Team";N5;IF(O5="company";M5;L5)) |
2 | 3 | Company 1 | =INDEX(L:L;MATCH(K6;J:J;0)) | =INDEX(M:M;MATCH(M6;L:L;0)) | =IF(ISNUMBER(SEARCH("team";L6));"Team";IF(ISNUMBER(SEARCH("company";L6));"Company";"Area")) | =IF(O6="Team";L6;"") | =IF(O6="Team";M6;IF(O6="company";L6;"")) | =IF(O6="Team";N6;IF(O6="company";M6;L6)) |
3 | 4 | Area 1 | =INDEX(L:L;MATCH(K7;J:J;0)) | =INDEX(M:M;MATCH(M7;L:L;0)) | =IF(ISNUMBER(SEARCH("team";L7));"Team";IF(ISNUMBER(SEARCH("company";L7));"Company";"Area")) | =IF(O7="Team";L7;"") | =IF(O7="Team";M7;IF(O7="company";L7;"")) | =IF(O7="Team";N7;IF(O7="company";M7;L7)) |
Solved! Go to Solution.
I solved this by myself using simply conditional columns and merge of columns.
Any ideas? Can this be done with measures or new columns or should I do some kind of merging? Any help highly appreciated. I can also provide additional info if needed.
I solved this by myself using simply conditional columns and merge of columns.
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 |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |