Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.