Convert rows to columns and expand all columns at once

[siva_powerbi]

Hello Experts

I have generated the tables as a list from List.Generate but my requirment is to convert rows in list as each row is a column and then expand all columns at once, when I try to expand indivudal columns then end result is like  a cross join.

Thanks

Siva

[v-alq-msft]

Hi, @siva_powerbi 

Based on your description, I created datat to reproduce your scenario. The pbix file is attached in the end,

Table:

Then you may expand the 'Column1'.

Finally you need to make the table selected, click 'Transpose' in 'Transform' ribbon and set data type for each column to make each row as a column.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[siva_powerbi]

@v-alq-msft Thanks Allan for the reply and work book.

My requirement is a bit different compared to the one added in report. 

I would apologize as I didn't mention in question, i will be having a single in the list with each row of the list is a Table.

I have updated the workbook and attached same. Also attached the image of the required output.

Data in source:

 Required output:

Unable to add the files as getting message pbix files are not supported, In the attached report in your answer need only column (CustomerID)

Thanks

Siva

[v-alq-msft · Accepted solution]

Hi, @siva_powerbi 

I modified data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may paste the following m codes in 'Advanced Editor' to apply the corresponding transformations.

let
    Source = Table.FromRecords(
        {
            [Column1=Table.FromRecords(
                {[ColumnID=1],[ColumnID=2],[ColumnID=3]}
            )],
            [Column1=Table.FromRecords(
                {[ColumnID=4],[ColumnID=5],[ColumnID=6]}
            )],
            [Column1=Table.FromRecords(
                {[ColumnID=7],[ColumnID=8],[ColumnID=9]}
            )]
        }
    ),
    #"Transposed Table" = Table.Transpose(Source),
    #"Added Custom" = Table.AddColumn(#"Transposed Table", "Custom", each let x = Table.RowCount([Column1]) in 
List.Generate(
    ()=>0,
    each _<=x-1,
    each _+1
)),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    Custom1 = Table.TransformRows(#"Expanded Custom",each let c = _[Custom] in
[Column1=_[Column1][ColumnID]{c},Column2=_[Column2][ColumnID]{c},Column3=_[Column3][ColumnID]{c}]

),
    #"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Column1", "Column2", "Column3"}, {"Column1.Column1", "Column1.Column2", "Column1.Column3"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Column1",{{"Column1.Column1", "Column1"}, {"Column1.Column2", "Column2"}, {"Column1.Column3", "Column3"}})
in
    #"Renamed Columns"

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[siva_powerbi]

Thanks for the answer, 

Sorry for the late reply, solved my problem.

Unable to understand the below mentioned code, can you please help to explan.

= Table.TransformRows(#"Expanded Custom",each let c = _[Custom] in
[Column1=_[Column1][ColumnID]{c},Column2=_[Column2][ColumnID]{c},Column3=_[Column3][ColumnID]{c}]

)

Thanks

Siva

[Fowmy]

@siva_powerbi 

Can you share a sample file with the same status as you have and show the expected output?

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn