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 importfunciton. Now i want to add rows for missing disctint values. Here is my example:

 

I want power query to check column "Nr" for distinct values, in this case nr 1 to 10. 
Then i want to add the missing values in each change of Source. 
So i would like to add rows for "File2.xlx" with the number 5,6 and 7. The color column for those rows can be blank or 0.

 

Is that possisble?

Thx in advanced.

 
 
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

     

2 Replies

  • 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