I have an ID column and I am looking for ways to increment my IDs each time a specific item appears in my Geography
column (ItalyZ
, ItalyM
, UKY
or UKM
) is found.
The ID of ItalyZ
starts at 0 and ends at 4000.
The ID of ItalyB
starts at 4000 and ends at 8000.
The ID of UKY
starts at 0 and ends at 4000.
The ID of UKM
starts at 4000 and ends at 8000.
However, I am refreshing my file, and I will thus have from time to time new arrivals of "geographies" without the origins or first IDs. These boundaries/ranges are only known beginning and ends.
Here is a sample of my data:
|---------------------|------------------|
| ID | Geography |
|---------------------|------------------|
| AB0000 | ItalyZ |
|---------------------|------------------|
| AB4041 | ItalyB |
|---------------------|------------------|
| BC0000 | UKY |
|---------------------|------------------|
| BC4001 | UKM |
|---------------------|------------------|
| NULL | ItalyZ |
|---------------------|------------------|
| NULL | ItalyZ |
|---------------------|------------------|
| NULL | UKY |
|---------------------|------------------|
| NULL | UKM |
|---------------------|------------------|
Here is my expected output :
|---------------------|------------------|
| ID | Geography |
|---------------------|------------------|
| AB0000 | ItalyZ |
|---------------------|------------------|
| AB4041 | ItalyB |
|---------------------|------------------|
| BC0000 | UKY |
|---------------------|------------------|
| BC4001 | UKM |
|---------------------|------------------|
| AB0001 | ItalyZ |
|---------------------|------------------|
| AB0001 | ItalyZ |
|---------------------|------------------|
| AB4042 | UKY |
|---------------------|------------------|
| BC0001 | UKM |
|---------------------|------------------|
I have been trying many various ways and trying to adapt running total solutions. I have also been trying to break apart my file in four different ones in order not to have an If function alternating between cases, and thus making it simpler, like this in my power query:
#"Added Custom2" = Table.AddColumn(#"Reordered Columns", "Sum", each if [Geography] = "UKM" then [Number AB range below 4000] + 1
else if [Geography] = "UKY" then [Number AB range above 4000] + 1
else if [Geography] = "ItalyB" then [Number BC range above 5000]
else [Number BC range below 5000] + 1)
But absolutely nothing works. This maddening.
Aucun commentaire:
Enregistrer un commentaire