Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hello community, I'm a new user of Power BI and would like to know how to move values from from column to additional columns that will contain only , closed, stand, none, enhanced values, see pictures, Please kindly help me. Thank you.
regards,
Joma
Solved! Go to 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])))
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.
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
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.
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
Thank you
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"?
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])))
Thank you it works 🙂
Yes that's the one
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
24 | |
13 | |
12 | |
11 | |
8 |
User | Count |
---|---|
43 | |
25 | |
16 | |
15 | |
12 |