Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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"))
Solved! Go to 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"
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 @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
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.
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.
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"
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
63 | |
58 | |
27 | |
17 | |
13 |