Reply
Super User
Posts: 754
Registered: ‎06-03-2016
Accepted Solution

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


Accepted Solutions
Super User
Posts: 1,620
Registered: ‎09-06-2015

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
Posts: 1,620
Registered: ‎09-06-2015

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


All Replies
Super User
Posts: 2,833
Registered: ‎09-27-2017

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 )
Super User
Posts: 754
Registered: ‎06-03-2016

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
Posts: 2,833
Registered: ‎09-27-2017

Re: Add Dynamic Rows in Power Query

@Baskar

 

I am attaching the sample file as well

 

baskar.png

Attachment
Super User
Posts: 2,833
Registered: ‎09-27-2017

Re: Add Dynamic Rows in Power Query

@Baskar

 

Tagging the Power Query Champs

@ImkeF
@MarcelBeug

Super User
Posts: 1,620
Registered: ‎09-06-2015

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




Super User
Posts: 754
Registered: ‎06-03-2016

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
Posts: 1,620
Registered: ‎09-06-2015

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




Super User
Posts: 754
Registered: ‎06-03-2016

Re: Add Dynamic Rows in Power Query

HI @ImkeF

https://files.fm/u/t8thktwz

 

Here i have uploaded sample power BI file.

Super User
Posts: 1,620
Registered: ‎09-06-2015

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