Dynamic Columns duplicating

[GoatTracker]

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"))

[ImkeF]

Hi @GoatTracker ,
great! And yes, I had no idea what kind of logic you wanted to implement there. 

[ImkeF · Accepted solution]

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"

[GoatTracker]

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.

[ImkeF]

Hi @GoatTracker ,
thanks, that helps.
May I ask what the desired result looks like? How many rows and columns do you expect?

[GoatTracker]

Hi @ImkeF 

From my example I'd expect only the 10 rows.

The quantity of columns would be the existing table+ 20 dynamic columns.

[ImkeF]

Hi @GoatTracker ,
sure - if you provide a file with some sample data I could then show you the solution on that model.

[GoatTracker]

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"

[ImkeF]

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.

[GoatTracker]

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.