Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Add rows based on distinct value

Hi,   I checked several post but couldnt find the answer I was looking for. So hopefully someone can help me or refer my to the right post.   I have a table which i fill with the folder importfu...
Need Help
  • Mariusz's avatar
    Mariusz
    6 years ago

    Hi Anonymous 

     

    Please see the below script or the attached for a solution.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsvMSTXUq8gprlDSUTIE4kSlWB00YSMgTsIUNgbiZExhEyBOwRQ2A+JUTGELIE7DFDY0ABLpSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Source.Name = _t, Nr = _t, x = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Source.Name", type text}, {"Nr", Int64.Type}, {"x", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Source.Name"}, {{"rows", each 
        let 
            rows = _,
            difference = List.Difference( { 1..10 }, rows[Nr] ),
            tableMissingNr = Table.FromList( difference, Splitter.SplitByNothing(), {"Nr"}, null, ExtraValues.Error),
            combine = Table.Combine( { rows, tableMissingNr })
        in 
            combine, type table [Source.Name=text, Nr=number, x=text]}}),
    #"Expanded rows" = Table.ExpandTableColumn(#"Grouped Rows", "rows", {"Nr", "x"}, {"Nr", "x"})
in
    #"Expanded rows"

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    LinkedIn

     

Mariusz's avatar
Mariusz
Community Champion
6 years ago

Hi Anonymous 

 

Please see the below script or the attached for a solution.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsvMSTXUq8gprlDSUTIE4kSlWB00YSMgTsIUNgbiZExhEyBOwRQ2A+JUTGELIE7DFDY0ABLpSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Source.Name = _t, Nr = _t, x = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Source.Name", type text}, {"Nr", Int64.Type}, {"x", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Source.Name"}, {{"rows", each 
        let 
            rows = _,
            difference = List.Difference( { 1..10 }, rows[Nr] ),
            tableMissingNr = Table.FromList( difference, Splitter.SplitByNothing(), {"Nr"}, null, ExtraValues.Error),
            combine = Table.Combine( { rows, tableMissingNr })
        in 
            combine, type table [Source.Name=text, Nr=number, x=text]}}),
    #"Expanded rows" = Table.ExpandTableColumn(#"Grouped Rows", "rows", {"Nr", "x"}, {"Nr", "x"})
in
    #"Expanded rows"

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Mariusz ,

 

Thank you for the quick respons!

 

I also had an additional question. But you solution also worked for that question.

So i deleted that reply :). I have the desired result now.


Kind regards,

 

Jan-Willem