Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • amitchandak's avatar
    amitchandak
    5 years ago

    Anonymous , Create two new columns like

    Number 1= if([Product]= "A", [Number], blank())
    Number 2= if([Product]= "B", [Number], blank())

amitchandak's avatar
amitchandak
Super User
5 years ago

Anonymous , Create two new columns like

Number 1= if([Product]= "A", [Number], blank())
Number 2= if([Product]= "B", [Number], blank())

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi when I tried this, it says that I have a 'Token RightParen expected' error. Any way to fix this?

  • DavisBI's avatar
    DavisBI
    Solution Specialist
    5 years ago

    Anonymous 

    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!