- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

State Numbers Converted to State Names
I am trying to create a column called 'State' that shows the state that a policy belongs to.
The column that I am using to populate 'State' is called 'StateIDsAll'. The 'StateIDsAll' has numerous numbers in it that correspond to states as seen below (1 = Alabama, 2 = Alaska, 3 = Arizona, etc)
The highlighted selection above has '1, 4' in it, so when the 'State' column is created, '1, 4' should read 'Alabama, Arkansas'
I have created a new table that has columns called 'State Number' and 'State Name', but I am not sure where to go after that.
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi @i820017
From this point, you need to create a relationship between the tables that you ate at.
In order to assist in a more informed manner
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Sorry for being unclear...I am trying to get a column that fills in the correct state for a state number. The state numbers column has already been entered. It comes from a Sharepoint site. It is titled 'StateIDsAll' in the link below.
https://www.dropbox.com/s/hdnr6n8as02p3ev/Screenshot%202023-06-22%20130445.png?dl=0
The highlighted part should read 'Alabama, Arkansas'
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Here is a Dropbox link to the PBIX file that shows what I am looking for.
It has 2 fields:
- State_Names_and_Numbers
- This field is the one that I am going to use in my Power BI Report
- State_Numbers_and_their_State_Names
- This field is an example of what I am looking for
The page entitled 'Completed Questions' has the field 'State_Numbers_and_their_State_Names' in it
Right now, the data that is getting loaded into the report that I am building has a number assigned to a state.
1 = Alabama, 2 = Alaska, 3 = Arizona, etc
The issue is that multiple numbers (and therefore multiple states) can show up in a row.
For example, in the second row of the 'Completed Questions' page, there are state numbers '1,2' which should show 'Alabama, Alaska' in the completed report.
I am trying to create a DAX column that will create the desired column called 'State Names'
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Based on the file, I couldn't figure out what you were trying to accomplish.
Are all possible combinations included in the data you are analyzing?
When a user chooses a country, are they choosing a number or a word?
Why does he choose combinations over countries, rather than a country followed by a combination?
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Here are the answers to your questions
Are all possible combinations included in the data you are analyzing?
No, all possible combinations are not included in the data that I am analyzing. I would use a SWITCH function if that were the case
When a user chooses a country, are they choosing a number or a word?
When a user chooses a state (not a country...I apologize for being unclear on that), then he is choosing a number. 1 = Alabama, 2 = Alaska, 3 = Arizona, etc
Why does he choose combinations over countries, rather than a country followed by a combination?
He is choosing states, not countries...He is assigning policies to certain states. For example, policy A might be assigned to 1 state or more than 1 state. Policy B might be assigned to 1 state or more than 1 state, etc
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi @i820017
From this point, you need to create a relationship between the tables that you ate at.
In order to assist in a more informed manner
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

Helpful resources
Subject | Author | Posted | |
---|---|---|---|
02-03-2025 08:19 AM | |||
02-27-2024 06:49 PM | |||
Anonymous
| 09-20-2017 08:38 AM | ||
01-27-2025 05:10 AM | |||
09-18-2024 07:39 PM |
User | Count |
---|---|
140 | |
110 | |
81 | |
60 | |
46 |