Forum Discussion

Resident Rockstar

8 years ago

Solved

Add Dynamic Rows in Power Query

Thanks Advanced !!! I have below datset with dens value, in this dataset dens value with missing contunious number. MattAllington Greg_Deckler Zubair_Muhammad

Need Help

ImkeF

8 years ago

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"

ImkeF

7 years ago

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"

ImkeF

Community Champion

8 years ago

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"

Baskar

Resident Rockstar

8 years ago

No words to express my happiness. ImkeF

Your the real guru in Power Query.

You are Champions of Champion.

ImkeF
Community Champion
8 years ago
Thank you Baskar!
Such a feedback keeps me going ;)

ImkeF

Community Champion

7 years ago

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"

Baskar
Resident Rockstar
7 years ago
Hi ImkeF

Sorry for asking question on this same issue. Now have to increase the value .2 in dense value.
I tried with your existing code but no luck for me. Can you please help me on this case.

Expected result attached in image.

ImkeF

Community Champion

7 years ago

Hi Baskar,

hope this works, otherwise please post link to sample data:

let

CreateRowsFunction = (Partition) =>
        let
            Source = Partition,
            #"Sorted Rows" = Table.Sort(Source,{{"Dense Value", Order.Ascending}}),
            AddedIndex = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1),
            #"Added Custom" = Table.AddColumn(AddedIndex, "Add", each try List.Transform( {1..AddedIndex[Dense Value]{[Index]+1} - [Dense Value] }, (x) => (x-1) * 0.2) otherwise {0}),
            #"Expanded Add" = Table.ExpandListColumn(#"Added Custom", "Add")
        in
            #"Expanded Add",

    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 Custom1" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Dense Value", "Count", "Add"}, {"Dense Value", "Count", "Add"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom1",{"Partition"}),
    #"Inserted Addition" = Table.AddColumn(#"Removed Columns", "Result", each [Dense Value] + [Add], type number)
in
    #"Inserted Addition"

Baskar
Resident Rockstar
7 years ago
Hi ImkeF

Now my Dense Value in decimals. So here i have increase the value by +.2
https://files.fm/u/tft2w9dr

Forum Discussion

Add Dynamic Rows in Power Query

Related content

Dynamic Public Holidays Table (Power Query / Power BI)

Power Query dates

Query Regarding Dynamic Queries

Data transform using power query

ayuda en power query