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"

Baskar

Resident Rockstar

8 years ago

Hi ImkeF ,

You are the real champion of Power Query. Tons of Thanks from me.

Need one clarification here. Is there any way to get continuous number in Dense instead of same number.

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"

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
8 years ago
No words to express my happiness. ImkeF
Your the real guru in Power Query.
You are Champions of Champion.
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