Forum Discussion
Solved
Dynamic Columns duplicating
I've got the following formula however, it duplicates exisitng data when I expand the table, how can I prevent it from doing this as it creates hundreds of thousands of rows. thanks in advance. ...
Hi GoatTracker ,
then you have to use a complete different approach, as you started with a cartesian product already:let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Task", type text}, {"Start", type date}, {"End", type date}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Start"}, #"Date Dimension", {"FullDateAlternateKey"}, "Date Dimension", JoinKind.LeftOuter), #"Added MonthStart" = Table.ExpandTableColumn(#"Merged Queries", "Date Dimension", {"MonthStart"}, {"MonthStart"}), #"Merged Queries1" = Table.NestedJoin(#"Added MonthStart", {"End"}, #"Date Dimension", {"FullDateAlternateKey"}, "Date Dimension", JoinKind.LeftOuter), #"Expanded Date Dimension" = Table.ExpandTableColumn(#"Merged Queries1", "Date Dimension", {"MonthStart"}, {"MonthEnd"}), #"Added Custom" = Table.AddColumn(#"Expanded Date Dimension", "Custom", each Record.Combine(List.Transform({1..20}, (l) => Record.AddField([], "Column" & Text.From(l), if [MonthStart]<=0 and [MonthEnd]>=0 then "x" else "y") ))), #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20"}) in #"Expanded Custom"
ImkeF
Community Champion
Community ChampionHi GoatTracker ,
then you have to use a complete different approach, as you started with a cartesian product already:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Task", type text}, {"Start", type date}, {"End", type date}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Start"}, #"Date Dimension", {"FullDateAlternateKey"}, "Date Dimension", JoinKind.LeftOuter),
#"Added MonthStart" = Table.ExpandTableColumn(#"Merged Queries", "Date Dimension", {"MonthStart"}, {"MonthStart"}),
#"Merged Queries1" = Table.NestedJoin(#"Added MonthStart", {"End"}, #"Date Dimension", {"FullDateAlternateKey"}, "Date Dimension", JoinKind.LeftOuter),
#"Expanded Date Dimension" = Table.ExpandTableColumn(#"Merged Queries1", "Date Dimension", {"MonthStart"}, {"MonthEnd"}),
#"Added Custom" = Table.AddColumn(#"Expanded Date Dimension", "Custom", each Record.Combine(List.Transform({1..20}, (l) => Record.AddField([], "Column" & Text.From(l), if [MonthStart]<=0 and [MonthEnd]>=0 then "x" else "y") ))),
#"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20"})
in
#"Expanded Custom"Hi ImkeF
That resolved the row issue, many thanks.
I'll need to work on resolving the formula as it only applies an "x" to the first row across all columns, then a "y" to the remaining column rows.
