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

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
MMoll
Frequent Visitor

Need help iterating over all columns

I have columns constaining a mix of records and lists. I need to convert them all to lists, collapse them into a table, and then expand them. (Thus extracting the contents of each list without disrupting the table structure). I have a function that works if I feed it hard-coded column names, but am having trouble with making it dynamic.

 

Current clunky proof-of-concept:

 

 

//In originating query:
ListConvert = Table.AddColumn(#"PreviousStep", "TableFromList", each #"TableFromLists"([ColumnName1],[ColumnName2],[ColumnName3],[etc.])),
DeleteOriginalColumns = Table.SelectColumns(ListConvert,{"TableFromList"})
TableExpanded =  Table.ExpandTableColumn(DeleteOriginalColumns, "TableFromList",{"(insert ","hard","coded","list","of","column","names","here","; ","I","need","to","dynamically","convert","column","record","names","to","text","strings)"})

in Table Expanded
// function #"TableFromLists:

(optional value1, optional value2, optional value3, optional value4, optional value5, optional value6, optional value7, optional value8, optional value9, optional value10)=>.

let
List1 = if value1 is null then {} else if Value.Is(value1, type {list}) then value1 else {value1},
List2 = if value2 is null then {} else if Value.Is(value2, type {list}) then value2 else {value2},
List3 = if value3 is null then {} else if Value.Is(value3, type {list}) then value3 else {value3},
List4 = if value4 is null then {} else if Value.Is(value4, type {list}) then value4 else {value4},
List5 = if value5 is null then {} else if Value.Is(value5, type {list}) then value5 else {value5},
List6 = if value6 is null then {} else if Value.Is(value6, type {list}) then value6 else {value6},
List7 = if value7 is null then {} else if Value.Is(value7, type {list}) then value7 else {value7},
List8 = if value8 is null then {} else if Value.Is(value8, type {list}) then value8 else {value8},
List9 = if value9 is null then {} else if Value.Is(value9, type {list}) then value9 else {value9},
List10 = if value10 is null then {} else if Value.Is(value10, type {list}) then value10 else {value10},

ColumnNames ={"ColumName1","ColumName2","ColumName3","ColumName4","ColumName5","ColumName6","ColumName7","ColumName8","ColumName9","ColumName10"}, //Still need to convert the records in arguments to text strings

Table = Table.FromColumns({List1,List2,List3,List4,List5,List6,List7,List8,List9,List10},ColumnNames),

in Table

 

 

 

I know I need to use some version of 

 

 

= Table.TransformColumns(#"PreviousStep",{{ Table.ColumnNames(Source),each if Record.Field(_, ColumnName) is null then {} else if Value.Is(_, type {list}) then _ else {_}}})

 

 

I can get it to work on one column, but fail to itereate over all columns.  I think I (a) don't have sufficient understanding of the "each" keyword when trying to apply it to both columns and rows and (b) am struggling to put the arguments in the correct record/list/table/text format (I get variations of "Cannot convert type function to type list", "Cannot convert type list to type text", etc.). 

 

Can anyone offer some insight? Thank you so much in advance.

3 REPLIES 3
ThxAlot
Super User
Super User

To my understanding, you try to combine all columns containing different data types.

let
    Source = #table({"Col1","Col2","Col3"},{{null,"ac",{1..3}},{{"A".."D"},123,""}}),
    #"Combined Columns" = Table.CombineColumns(
        Source,
        Table.ColumnNames(Source), each Table.FromColumns(List.Transform(_, each if Value.Is(_, type list) then _ else {_})),
        "Combined"
    ),
    #"Expanded Combined" = Table.ExpandTableColumn(#"Combined Columns", "Combined", {"Column1", "Column2", "Column3"})
in
    #"Expanded Combined"


Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LearnAndPractise(Everyday)


)



MMoll
Frequent Visitor

Yes; that's exactly what I'm trying to do. But I need the list of columns (and/or column names) to be dynamic so I can set it up as a function.

 

let
    fx_TableTransfermation = (t as table) =>
        let
            #"Combined Columns" = Table.CombineColumns(
                t,
                Table.ColumnNames(t), each Table.FromColumns(List.Transform(_, each if Value.Is(_, type list) then _ else {_})),
                "Combined"),
            #"Expanded Combined" = Table.ExpandTableColumn(#"Combined Columns", "Combined", List.Transform({1..Table.ColumnCount(t)}, each "Column"&Text.From(_)))
        in
            #"Expanded Combined",

    Source = Table.FromColumns({{null,"ac",{1..3}},{{"A".."D"},123,""},{1,{"u".."z"}}}),
    Invocation = fx_TableTransfermation(Source)
in
    Invocation

 

ThxAlot_0-1683824589211.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LearnAndPractise(Everyday)


)



Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

September Power BI Update Carousel

Power BI Monthly Update - September 2025

Check out the September 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

Top Kudoed Authors