Add rows based on distinct value

[Anonymous]

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.

[Mariusz · Accepted solution]

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]

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