Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.LeanAndPractise(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.LeanAndPractise(Everyday) ) |
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
39 | |
22 | |
20 | |
13 | |
13 |