cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Baskar Super Contributor
Super Contributor

Add Dynamic Rows in Power Query

Thanks Advanced !!!

 

I have below datset with dens value, in this dataset dens value with missing contunious number.

 

 

EMMA-154-Op.PNGCurrent DatasetEMMA-154-Ex OP.PNGExpected Dataset

 

@MattAllington @Greg_Deckler @Zubair_Muhammad

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Add Dynamic Rows in Power Query

Sorry Baskar,

my fault. Here it comes:  

 

let

CreateRowsFunction = (Partition) =>
        let
            Source = Partition,
            AddedIndex = Table.AddIndexColumn(Source, "Index", 0, 1),
            #"Added Custom" = Table.AddColumn(AddedIndex, "Custom", each try { [Dense Value] .. AddedIndex[Dense Value]{[Index]+1}-1 } otherwise {[Dense Value]}),
            #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
            #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom.1", each if [Custom]=[Dense Value] or [Custom] = null then [Count] else 0)
        in
            #"Added Custom1",

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc+xEQAhCATAXogNHk5RanHsv40HP/EluWBn4GBOsgcM61TIRDxZaJUf4/FEZvbMGjvGOHgECDwrEmtwu3k3StIoRD3YHLpGY7u1xwrVxLxHEn+fI/k+2y9ZLw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Ticket = _t, #"Dense Value" = _t, Count = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Dense Value", Int64.Type}, {"Count", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Ticket"}, {{"Partition", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each CreateRowsFunction([Partition])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Custom", "Custom.1"}, {"Dense Value", "Count"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Partition"})
in
    #"Removed Columns"

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

Super User
Super User

Re: Add Dynamic Rows in Power Query

Please check out this new code:

 

let

CreateRowsFunction = (Partition) =>
  
      let
            Source = Table1,
            #"Sorted Rows" = Table.Sort(Source,{{"Dense Value", Order.Ascending}}),
            AddedIndex = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1),
    Custom1 = Table.AddColumn(AddedIndex, "Intervals", each try (AddedIndex[Dense Value]{[Index]+1}*10 - [Dense Value]*10) / 2 otherwise 0),
    #"Added Custom1" = Table.AddColumn(Custom1, "Custom", each {0..[Intervals] - 1}),
    #"Added Custom" = Table.AddColumn(#"Added Custom1", "Add", each List.Transform([Custom], (x) => x * 0.2)),
    #"Expanded Add" = Table.ExpandListColumn(#"Added Custom", "Add"),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Add",null,0,Replacer.ReplaceValue,{"Add"}),
    #"Inserted Addition" = Table.AddColumn(#"Replaced Value", "NewDense", each [Dense Value] + [Add], type number),
    #"Removed Other Columns" = Table.SelectColumns(#"Inserted Addition",{"Ticket", "Dense Value", "Count", "NewDense"})
in
    #"Removed Other Columns"

,

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI20jMCUkZKsTpwATMQhRAw1rMAUiYIARMgYYrEBWswQxYAaTBHCJiiypuCrTRFFkCz0hRsAtBRsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Ticket = _t, #"Dense Value" = _t, Count = _t]),
    #"Changed Type1" = Table.TransformColumnTypes(Source,{{"Dense Value", type number}, {"Count", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"Ticket"}, {{"Partition", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each CreateRowsFunction([Partition])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Dense Value", "Count", "NewDense"}, {"Dense Value", "Count", "NewDense"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Partition"})
in
    #"Removed Columns"

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

17 REPLIES 17
Super User
Super User

Re: Add Dynamic Rows in Power Query

Hi @Baskar

 

Sorry I am not very good in Power Query

 

But this DAX calculated table should work

 

Calculated Table =
VAR temp =
    GENERATE (
        VALUES ( Table1[Ticket_INTL_ID] ),
        VAR maxvalue =
            MAXX ( RELATEDTABLE ( Table1 ), [Dens_Value] )
        VAR minvalue =
            MINX ( RELATEDTABLE ( Table1 ), [Dens_Value] )
        RETURN
            SELECTCOLUMNS (
                EXCEPT (
                    GENERATESERIES ( minvalue, maxvalue ),
                    CALCULATETABLE ( VALUES ( Table1[Dens_Value] ) )
                ),
                "Dense_Value", [Value]
            )
    )
VAR temp1 =
    ADDCOLUMNS ( temp, "Dense Count", 0 )
RETURN
    UNION ( Table1, temp1 )
Baskar Super Contributor
Super Contributor

Re: Add Dynamic Rows in Power Query

Thanks for very quick response @Zubair_Muhammad.

 

Here am looking for power query operation. after that i have few ETL operations.

Super User
Super User

Re: Add Dynamic Rows in Power Query

@Baskar

 

I am attaching the sample file as well

 

baskar.png

Super User
Super User

Re: Add Dynamic Rows in Power Query

@Baskar

 

Tagging the Power Query Champs

@ImkeF
@MarcelBeug

Super User
Super User

Re: Add Dynamic Rows in Power Query

1) Add an index column to "fake" a row index, starting from 0

2) Add a column where you create a list of values that cover the range up until the next row like this: 

 { [Dense Value] .. <YourPreviousStepName>[Dense Value]{[Index]+1}-1 }

3) Expand this list-column. It will return the missing values. You can then add a column for a new value where you compare if the value of the new column equals column "Dense Value". If so, take the original amount and if not, take 0.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Baskar Super Contributor
Super Contributor

Re: Add Dynamic Rows in Power Query

Thanks for your valuable response @ImkeF.

 

Am begginer on power query, I will explain in little bit brief on my problem.

 

EMMA-154-Ex OP.PNGCurrent vs Expected

Can you please help me to here. stucked with last 3 days with this scenario. 

 

Thanks a lot.

Super User
Super User

Re: Add Dynamic Rows in Power Query

You need a function for it. Please post link to sample data and I will mockup a solution for you.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Baskar Super Contributor
Super Contributor

Re: Add Dynamic Rows in Power Query

HI @ImkeF

https://files.fm/u/t8thktwz

 

Here i have uploaded sample power BI file.

Super User
Super User

Re: Add Dynamic Rows in Power Query

Hi Baskar,

please paste this code into the advanced editor:

 

let

CreateRowsFunction = (Partition) =>
        let
            Source = Partition,
            AddedIndex = Table.AddIndexColumn(Source, "Index", 0, 1),
            #"Added Custom" = Table.AddColumn(AddedIndex, "Custom", each try { [Dense Value] .. AddedIndex[Dense Value]{[Index]+1}-1 } otherwise {}),
            #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
            #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom.1", each if [Custom]=[Dense Value] or [Custom] = null then [Count] else 0)
        in
            #"Added Custom1",

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc+xEQAhCATAXogNHk5RanHsv40HP/EluWBn4GBOsgcM61TIRDxZaJUf4/FEZvbMGjvGOHgECDwrEmtwu3k3StIoRD3YHLpGY7u1xwrVxLxHEn+fI/k+2y9ZLw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Ticket = _t, #"Dense Value" = _t, Count = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Dense Value", Int64.Type}, {"Count", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Ticket"}, {{"Partition", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each CreateRowsFunction([Partition])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Dense Value", "Custom.1"}, {"Dense Value", "Count"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Partition"})
in
    #"Removed Columns"

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 481 members 3,944 guests
Please welcome our newest community members: