To expand all lists in a row of lists at the same time without recurring values

0 votes

I was provided with this succinct and helpful code:

let
Source = #table({"A", "B"}, {{ {1,2}, {3,4}} }),
Expanded = List.Accumulate(
    Table.ColumnNames(Source), 
    Source, 
    (state, column) => Table.ExpandListColumn(state, column))
in
Expanded

Which yields the following result:

I don't want recurrent values in previously processed columns as each follow-on column is processed. I would like to get the following result instead:

Is there a simple modification to the given code that will suffice?

Thanks(:

Feb 19 in Power BI by Shubham
• 13,290 points
172 views

1 answer to this question.

0 votes

Can't promise simple, but I have something that will work for sure. 

Objective : You need a code that can combine the columns with an adjusted code so that;

  • lists are zipped 
  • Inner lists are transformed into records

Step 1 : Expand the list column. This results in a column with nested records which are subsequently expanded.

Step 2 : Create some Dummy text columns that are combined in order to generate the base code (Since you can't use combine columns with nested lists).

Step 3 : Eventually, adjust and remove the steps with the dummy columns.

Code :

let
    Source = #table({"A", "B"}, {{ {1,2}, {3,4}} }),
    #"Merged Columns" = Table.CombineColumns(Source,{"A", "B"}, each List.Transform(List.Zip(_), each Record.FromList(_,{"A","B"})),"Merged"),
    #"Expanded Merged" = Table.ExpandListColumn(#"Merged Columns", "Merged"),
    #"Expanded Merged1" = Table.ExpandRecordColumn(#"Expanded Merged", "Merged", {"A", "B"}, {"A", "B"})
in
    #"Expanded Merged1"
answered Feb 19 by Upasana
• 8,530 points

Related Questions In Power BI

0 votes
1 answer

Calculated column with the sum of values from many columns in a row

Hi, You can create a new column by ...READ MORE

answered Mar 14 in Power BI by Cherukuri
• 25,820 points
39 views
0 votes
1 answer

What are the components I need to excel to become a pro in power BI

Having knowledge about these topics is a must.  Power ...READ MORE

answered Oct 8, 2018 in Power BI by Kalgi
• 40,440 points
45 views
0 votes
1 answer

Add Column Count_of_people across two tables to get the count of people from a city.

Hi Anitha, 1. Merge both tables, using City ...READ MORE

answered Mar 22 in Power BI by Cherukuri
• 25,820 points
25 views
0 votes
1 answer

Why does a reference to a value in previous row throw this Expression.Error?

If you have multiple steps after the ...READ MORE

answered May 8 in Power BI by Avantika
• 1,500 points
27 views
0 votes
1 answer

Power Query: Adding a Special Case

Try adding a Group By with Minimum ...READ MORE

answered Dec 27, 2018 in Power BI by Upasana
• 8,530 points
39 views
0 votes
1 answer

Power Query : Adding Columns and Multiple files

The following call, FirstRowAsHeader = Table.PromoteHeaders(TableWithoutHeader) shall replace the ...READ MORE

answered Feb 14 in Power BI by Upasana
• 8,530 points
22 views
0 votes
1 answer

Replace “yes” found in one list with the actual string from another list

Objective : The idea is to use List.Transform twice, ...READ MORE

answered Feb 19 in Power BI by Upasana
• 8,530 points
42 views
0 votes
1 answer

PowerQuery : How can I reference a cell's value?

You can do so using a named ...READ MORE

answered Feb 27 in Power BI by Shubham
• 13,290 points
313 views
0 votes
1 answer

Dax: Sum of values from many columns in a row

Yes, you can. Try to "Unpivot other columns" ...READ MORE

answered Apr 3 in Power BI by Upasana
• 8,530 points
292 views
0 votes
1 answer

Calculate Time Intersection to Correlate Sequences of Independent Events

I think this solution requires a CROSS JOIN implementation. ...READ MORE

answered Oct 26, 2018 in Power BI by Upasana
• 8,530 points
42 views