Forum Discussion
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.
= List.Accumulate({1..20}, #"Expanded Date Dimension1", (s,c) => Table.AddColumn(s, "Column"&Text.From(c), each if [MonthStart]<=0 and [MonthEnd]>=0 then "x" else "y"))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"
9 Replies
Hi GoatTracker ,
you have to include the expansion of the newly created column in the List.Accumulate-function (last parameter itself). So that the next column will be added after the previous one has been expanded.Hi ImkeF
Thank you for your response, I don't understand what you are recommending sorry, can you elaborate please?
When I select the table to expend and select the new columns (only), this is when it duplicated the existing rows.
Hi GoatTracker ,
sure - if you provide a file with some sample data I could then show you the solution on that model.Hi ImkeF ,
I have attached the basic table with 10 rows, the code below blows this out to over 1.3 million rows once I expend the table, so I haven't done this part in the example 🙂
Here is the example file: Example File
Thank you for your time.
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 List.Accumulate({1..20}, #"Expanded Date Dimension", (s,c) => Table.AddColumn(s, "Column"&Text.From(c), each if [MonthStart]<=0 and [MonthEnd]>=0 then "x" else "y"))) in #"Added Custom"
Hi GoatTracker ,
thanks, that helps.
May I ask what the desired result looks like? How many rows and columns do you expect?Hi ImkeF
From my example I'd expect only the 10 rows.
The quantity of columns would be the existing table+ 20 dynamic columns.
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"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.
Hi GoatTracker ,
great! And yes, I had no idea what kind of logic you wanted to implement there.
