Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
GoatTracker
Frequent Visitor

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"))
1 ACCEPTED SOLUTION
ImkeF
Super User
Super User

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"

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

9 REPLIES 9
ImkeF
Super User
Super User

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

ImkeF
Super User
Super User

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"

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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
Super User
Super User

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

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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
Super User
Super User

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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
Super User
Super User

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.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.