separate values into columns

[jomask]

Hello community, I'm a new user of Power BI and would like to know how to move values from  from StateofProcessing column to additional columns that will contain only , closed, stand, none, enhanced values, see pictures, Please kindly help me. Thank you.

The link contains files excel, power bi file and picture, if you can pls respond with a picture.

https://easyupload.io/zoci4s 

regards,

Joma

[dufoq3 · Accepted solution]

Like this? Pbix file attached.

[dufoq3]

Could you explain it in detail please? I don't understand why do you need that. There is no "open" value in [StateOfProceedingID] column.

If you want to add new column for each row with values from [StateOfProceedingID] column do this:

let
    Source = Excel.Workbook(File.Contents("C:\Downloads\PowerQueryForum\jomask\Book1.xlsx"), true, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    States = List.Distinct(Sheet1_Sheet[StateOfProceedingID]),
    StepBack = Sheet1_Sheet,
    Ad_CaseColumns = List.Accumulate(
        {0..List.Count(States)-1},
        StepBack,
        (s,c)=> Table.AddColumn(s, "Case " & Text.From(c+1), (x)=> States{c}, type text))
in
    Ad_CaseColumns

If you want add 5 columns with values None, Closed, Enchanced, Open, Pending - use this:

let
    Source = Excel.Workbook(File.Contents("C:\Downloads\PowerQueryForum\jomask\Book1.xlsx"), true, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    Ad_CaseColumns = [ values = {"None", "Closed", "Enchanced", "Open", "Pending"},
    la =  List.Accumulate(
                List.Zip({ {0..List.Count(values)-1}, values }),
                Sheet1_Sheet,
                (s,c)=> Table.AddColumn(s, "Case " & Text.From(c{0}+1), (x)=> c{1}, type text))
  ][la]
in
    Ad_CaseColumns

Don't forget to replace address to your excel file.

[jomask]

I would like to use this code above for this query as I dont use Excel sheets but SQL tables, please can you help me with this. I try to fidle around how to change source  and fit in your code but it makes errors...

The Result would be like this 

[jomask]

Hello Thank you for a response. I know there is no "open" just quickly created a sample and snipped a picture. I would like those "values"(I turned them into the non-digital text, originally they are digital 1,2,3,4 each number belongs to Closed, enhanced...) Closed, enhanced, none, stand put the next each other in columns in Power BI with a Column names "case 1", "case 2", "case 3" and "case 4". The picture in Excel ( only for explanation my idea) with the red arrow is what I need in Power Bi.

[dufoq3 · Accepted solution]

Like this? Pbix file attached.

[jomask]

Hello, one more question , I would liked to do a Pie chart from the Case column. It would show(count) Case1,Case2,Case3,Case4,Case5. How can I do that? Once again Thank you

[jomask]

Thank you

[dufoq3]

You're welcome.

[jomask]

 

Hello,

When I add the step "Values" it says "expression error:the name 'PromoteHeaders' wasn't recognised. Make sure you spelled correctly..... but my step before is "Promoted Headers"... How did you make step *Values"?

[dufoq3 · Accepted solution]

Hi, if you have space in Promoted Headers, you have to use #"Promoted Headers" as reference so replace Values step with this:

= List.Buffer(List.Sort(List.Distinct(#"Promoted Headers"[StateOfProceedingID])))

[jomask]

Thank you it works 🙂

[jomask]

Yes that's the one

[v-junyant-msft]

Hi @jomask ,

If @dufoq3 's solution works for you, please accept the reply as Solution so that more users facing the same problem can find it faster. Thank you!

Best Regards,
Dino Tao

[lbendlin]

Resist the urge to do that.  It is not necessary.  Ingest your data as is and let the visuals do the pivoting work for you.