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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

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
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

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

June 2025 community update carousel

Fabric Community Update - June 2025

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