Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
FCD
Regular Visitor

Add Variable quantity of columns and transpose data

Dear All,

I have a table called table1 with 2 differents columns (called Letter and Type)

I wish I can create a new table (table2) from this table containing in the first colum all the distinct Types and next as many columns as the maximum qty of different Letters in each type (n).

Each column will be called like KW1, KW2, …, KWn

Then the values of Types will be added in the different columns. If there is no value, the cell will remain empty.

 

See attached screenshot to show what I expect to get.

Thank you for help.

Best RegardsExpectedResult.GIF

1 ACCEPTED SOLUTION
slorin
Super User
Super User

Hi @FCD 

 

= Table.Combine(
Table.Group(Your_Source,
{"Type"},
{{"Data", each Table.FromRows(
{{[Type]{0}} & [Letter]},
{"Type"} & List.Transform({1..Table.RowCount(_)}, each "KW" & Text.From(_)))
}})
[Data])

 Stéphane

View solution in original post

2 REPLIES 2
FCD
Regular Visitor

Thank you very much. Just what I expected

slorin
Super User
Super User

Hi @FCD 

 

= Table.Combine(
Table.Group(Your_Source,
{"Type"},
{{"Data", each Table.FromRows(
{{[Type]{0}} & [Letter]},
{"Type"} & List.Transform({1..Table.RowCount(_)}, each "KW" & Text.From(_)))
}})
[Data])

 Stéphane

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors