Forum Discussion
Splitting columns based on data from another column
Hi all, I have data that looks something like this: Product Number A 1 A 2 A 3 A 4 B 1 B 2 B 3 How do I manipulate or split the columns so it looks someth...
Anonymous , Create two new columns like
Number 1= if([Product]= "A", [Number], blank())
Number 2= if([Product]= "B", [Number], blank())
Hi when I tried this, it says that I have a 'Token RightParen expected' error. Any way to fix this?
DavisBI
Solution Specialist
Solution SpecialistAnonymous
Suppose you have the following data:
let
DATA =
Table.FromRecords(
{
[ID = "A", Number = 12],
[ID = "A", Number = 5],
[ID = "A", Number = 6],
[ID = "A", Number = 8],
[ID = "B", Number = 14],
[ID = "B", Number = 9],
[ID = "B", Number = 7],
[ID = "B", Number = 7],
[ID = "C", Number = 5],
[ID = "C", Number = 16],
[ID = "C", Number = 18]
},
type table [ID = nullable text, Number = nullable number]
)
in
DATA
And then you can create a new function using M code:
(ID_Code as text) =>
let
DATA =
DATA,
DATA_Filtered = Table.SelectRows(DATA, each ([ID] = ID_Code)),
DATA_Renamed = Table.RenameColumns(DATA_Filtered,{{"Number", ID_Code}})
in
DATA_Renamed
Since that, you need to create a list that including all distinct values of [ID] so that PowerQuery can run the function for each [ID] after you click "Invoke Custom Function" and you'll get what you want in the end. This is a full dynamic solution.
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!