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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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.LeanAndPractise(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.LeanAndPractise(Everyday)


)



Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors