Forum Discussion
Expanding Table to Columns
I am trying to expand the table to columns, but it is not working, how can I do that go through each cell and expand it to columns and skip the cell that has null or blank values.
Columns names are consistent everywhere.
I tried this code but no luck :
= Table.ExpandRecordColumn(#"Renamed Columns","Schedule F1", Table.ColumnNames(Table.FromRecords(List.Select(Table.Column(#"Renamed Columns","Schedule F1"), each _<> "" and _<> null))), Table.ColumnNames(Table.FromRecords(List.Select(Table.Column(#"Renamed Columns","Schedule F1"), each _<> "" and _<> null))))
Hi parthpatel4
I guess you probably don't want to filter out rows with blank values, so you can first replace blank values with an empty table which only has column headers "BeginningAmt" and "EndingAmt". Then expand this table column.
let Source = Table.FromRecords( {[Name="Bill", Schedule F1=#table({"BeginningAmt", "EndingAmt"}, {{100,220}})], [Name="Barb", Schedule F1=#table({"BeginningAmt", "EndingAmt"}, {{120,200}})], [Name="Margo", Schedule F1=#table({"BeginningAmt", "EndingAmt"}, {{300,400}})], [Name="Jeff", Schedule F1=#table({"BeginningAmt", "EndingAmt"}, {{300,180}})]}, type table [ Name = text, Schedule F1 = table ]), #"Appended Query" = Table.Combine({Source, Query2}), // Replace blank values with an empty table #"Replaced Value" = Table.ReplaceValue(#"Appended Query","",#table({"BeginningAmt", "EndingAmt"}, {{null,null}}),Replacer.ReplaceValue,{"Schedule F1"}), // Expand table column Custom1 = Table.ExpandTableColumn(#"Replaced Value", "Schedule F1", {"BeginningAmt", "EndingAmt"}, {"BeginningAmt", "EndingAmt"}) in Custom1Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
2 Replies
You should be able to filter out rows with blank using the drop down filter button. Also, if each "Table" has a single row (like the other pic suggests), you can add a custom column with an expression like below to extract the first record (and then expand that).
= [Schedule T1]{0}
Pat
Hi parthpatel4
I guess you probably don't want to filter out rows with blank values, so you can first replace blank values with an empty table which only has column headers "BeginningAmt" and "EndingAmt". Then expand this table column.
let Source = Table.FromRecords( {[Name="Bill", Schedule F1=#table({"BeginningAmt", "EndingAmt"}, {{100,220}})], [Name="Barb", Schedule F1=#table({"BeginningAmt", "EndingAmt"}, {{120,200}})], [Name="Margo", Schedule F1=#table({"BeginningAmt", "EndingAmt"}, {{300,400}})], [Name="Jeff", Schedule F1=#table({"BeginningAmt", "EndingAmt"}, {{300,180}})]}, type table [ Name = text, Schedule F1 = table ]), #"Appended Query" = Table.Combine({Source, Query2}), // Replace blank values with an empty table #"Replaced Value" = Table.ReplaceValue(#"Appended Query","",#table({"BeginningAmt", "EndingAmt"}, {{null,null}}),Replacer.ReplaceValue,{"Schedule F1"}), // Expand table column Custom1 = Table.ExpandTableColumn(#"Replaced Value", "Schedule F1", {"BeginningAmt", "EndingAmt"}, {"BeginningAmt", "EndingAmt"}) in Custom1Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
