Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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.
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) ) |
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
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LearnAndPractise(Everyday) ) |
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
13 | |
13 | |
10 | |
8 | |
7 |