cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

Create columns dynamically from column values

Hello community

 

I'm trying to create new columns in a table from the values in one column in the table.

 

I have a data set as it is shown below:

 

 

FromColumns.PNG

 

And I want to get new columns from the values as it is shown in red.

 

NewColumns.PNG

 

I wish to do it automatically.

 

How can I do that?

 

Thanks in advance for your time

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion
Community Champion

Re: Create columns dynamically from column values

In the Query Editor (M), you can select Column1 and choose "Pivot Column" on the Transform tab.

 

Maybe that is what you are looking for, however your result columns are empty and COLUMN1 is still in the result tabel.

 

"Pivot Column" typically aggregates values from another column (e.g. COLUMN2) in the new columns per value in COLUMN1, and then reduce rows so there are no duplicate combinations in the other columns (COLUMN3 and COLUMN4) in your example.

COLUMN1 as such would not be in the result table.

 

Edit: alternatively you can use the following code in the Advanced Query Editor, in which "TableName" is the name of the previous step:

    AddedColumns = Table.SelectColumns(TableName,List.Union({Table.ColumnNames(TableName),List.Distinct(TableName[COLUMN1])}), MissingField.UseNull)

 

 

Specializing in Power Query Formula Language (M)

View solution in original post

3 REPLIES 3
Highlighted
Community Champion
Community Champion

Re: Create columns dynamically from column values

In the Query Editor (M), you can select Column1 and choose "Pivot Column" on the Transform tab.

 

Maybe that is what you are looking for, however your result columns are empty and COLUMN1 is still in the result tabel.

 

"Pivot Column" typically aggregates values from another column (e.g. COLUMN2) in the new columns per value in COLUMN1, and then reduce rows so there are no duplicate combinations in the other columns (COLUMN3 and COLUMN4) in your example.

COLUMN1 as such would not be in the result table.

 

Edit: alternatively you can use the following code in the Advanced Query Editor, in which "TableName" is the name of the previous step:

    AddedColumns = Table.SelectColumns(TableName,List.Union({Table.ColumnNames(TableName),List.Distinct(TableName[COLUMN1])}), MissingField.UseNull)

 

 

Specializing in Power Query Formula Language (M)

View solution in original post

Highlighted
Frequent Visitor

Re: Create columns dynamically from column values

I have to bind values to slicer based on value selection in another slicer. For achieving this  I am getting data from different table but if i choose value in slicer that is not reflecting in all the visuals of report.

Highlighted
Community Champion
Community Champion

Re: Create columns dynamically from column values

@Deepa that looks like something completely different, so why not just start a new topic?

Specializing in Power Query Formula Language (M)

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors