Power BI Costum Column incrementing data in one sheet based on values in another sheet Text.Combine

Tom224

New Member
Joined
Mar 9, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
I am using Power BI to get data from a file, and look in another file. Based on specific conditions (four), I need to look at my antoher file, and increment specific ID, which I like to append to the matching columns.
I am not trying to merge the files, but only to look for information in one, and then add a number.
Here are my four conditions: - ItalyZ - ItalyB - UKY - UKM
In my other file, I am looking in the ID colum, and searching for these amount:
  • The highest number between ABE0000 and ABE4000, once found, I add one to AB for each "ItalyZ" elements. i.e AB0003 for one element "ItalyZ", the next "ItalyZ" wil get AB0004
  • The highest number between ABE4000 and ABE9000, once found, I add one to AB for each "ItalyB" elements. i.e AB4003 for one element "ItalyB", the next "ItalyB" wil get AB0004
  • The highest number between BC0000 and BC4000, once found, I add one to BC for each "UKY" elements. i.e BC0003 for one element "UKY", the next "UKY" wil get BC0004
  • The highest number between BC4000 and BC9000, once found, I add one to for each "UKM" elements. i.e BC4003 for one element "UKM", the next "UKM" wil get BC4004
Here my input files:

Input.png


Input 2.png


Input 3.png



I have been tying nested IF and "Table." in order to look in other sheet, but I am having difficulties. My main difficulty is that it is in another sheet. Selecting the letters, breaking the columns and then increment for specific condition would be ok but the fact that this located in another file is an issue.


I have started moving in this direction for example:

Rich (BB code):
If [#"Geography "] = UKM, Table.Combine
In 
Text.Combine( 
    Table.Combine(
        Table.SelectRows(Shee1,
        each Text.Contains(AB


What could I do to solve this ?
 

Attachments

  • Input 2.png
    Input 2.png
    3.5 KB · Views: 6

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top