Adding rows

[Anonymous]

Hello dear community members!

I am pretty new to PowerBi and I have already faced with a difficult task but I cannot solve it. Could someone please help me?

So this is a small sample of my data

Column1	Column2	Column3	Column4
A	DEFAULT	X	101
A	DEFAULT	X	102
A	NAME	X	101

The problem is that some codes like code 101 have both atrributes DEFAULT AND NAME but some codes like 102 have only DEFAULT attribute. I would like to create for those values which do not have the atrribute called NAME an additional row which would state null in Column3.

This would be my desired output

Column1	Column2	Column3	Column4
A	DEFAULT	X	101
A	DEFAULT	X	102
A	NAME	X	101
A	NAME	null	101

I hope that I stated everything clearly.

Thanks in advance,

Krišjānis

[ziying35]

@Anonymous 

let
    Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("i65Wcs7PKc3NM1SyUnJU0oHyjIA8F1c3x1CfELiYMVAsAs4zUbIyNDCs1aFIvxE+/X6Ovq74LY8FAA==",BinaryEncoding.Base64),Compression.Deflate))),
    fx = (tbl)=>
       let
          diff = List.Difference({"NAME", "DEFAULT"},tbl[Column2])
       in if diff{0}?=null then tbl else Table.FromRecords({tbl{0}, tbl{0}&[Column2=diff{0}, Column3=null]}),
    group = Table.Group(Source, "Column4", {"t", fx})[t],
    result = Table.Combine(group)
in
    result

[Anonymous]

you could do it all using the GUI.
All you need is a pivot and an un-pivot with a value change in between.

this is the code (I stole the table from the post @ziying35 )

let
    Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("i65Wcs7PKc3NM1SyUnJU0oHyjIA8F1c3x1CfELiYMVAsAs4zUbIyNDCs1aFIvxE+/X6Ovq74LY8FAA==",BinaryEncoding.Base64),Compression.Deflate))),
    #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[Column2]), "Column2", "Column3"),
    #"Replaced Value" = Table.ReplaceValue(#"Pivoted Column",null,"",Replacer.ReplaceValue,{"DEFAULT", "NAME"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {"Column1", "Column4"}, "Attribute", "Value")
in
    #"Unpivoted Columns"

then select the DEFAULT and NAME columns

and apply unpivots columns (1° item)

[Nishantjain]

@Anonymous  The code should work as well. Any questions let me know

Change the first step "Source" to your source of data

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXJxdXMM9QkBsiKA2NDAUClWB7uMEVzGz9HXFVlDLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),

    //Find data with no "NAME"
    #"Filtered Rows1" = Table.SelectRows(Source, each [Column2] = "NAME"),
    #"Removed Other Columns1" = Table.SelectColumns(#"Filtered Rows1",{"Column4"}),
    #"Column4 with No Name" = Table.Distinct(#"Removed Other Columns1"), 


    //Create additional row for data with no "NAME"

    #"Merged Queries" = Table.NestedJoin(Source, {"Column4"}, #"Column4 with No Name", {"Column4"}, "Table1 (2)", JoinKind.LeftOuter),
    #"Expanded Table1 (2)" = Table.ExpandTableColumn(#"Merged Queries", "Table1 (2)", {"Column4"}, {"Column4.1"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Table1 (2)", each [Column4.1] = null),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Column1", "Column4"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Column2", each "NAME"),
    #"Rows with No Name" = Table.AddColumn(#"Added Custom", "Column3", each "Null"), 

    // Append addtional lines
    #"Appended Query" = Table.Combine({Source, #"Rows with No Name"})
in
    #"Appended Query"

Thanks

Nishant

[edhans]

Hi @Anonymous ,

See if the M code below is what you need. It turns this:

Into this:

It does this by doing the following:

1. Groups the data by column 1 and 4 and puts all of the data in a new All Rows aggregation.
2. Counts the rows. If there is only 1 row, it assumes the Name row is missing, which is used in the next step.
3. It creates a new row using the Table.FromRecords table constructor that will have your original Col1 and Col4 value, plus the "Name" in col 2. It leaves col3 out.
4. It then appends the table in step 3 with the nested table in the [All Rows] column only if that nested table has 1 row. Because column 3 is missing from the table created in step 3, it is appended as a null.
5. I remove all but the last column, and expand all columns in the table created in step 4.

Data types get destroyed in this type of thing, so you'll need to re-apply types at the end before loading to DAX.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXJxdXMM9QkBsiKA2NDAUClWB7uMEVzGz9HXFVlDLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Column1", "Column4"}, {{"All Rows", each _, type table [Column1=nullable text, Column2=nullable text, Column3=nullable text, Column4=nullable text]}}),
    #"Added Row Count" = Table.AddColumn(#"Grouped Rows", "Row Count", each Table.RowCount([All Rows])),
    #"New Row" = 
        Table.AddColumn(
            #"Added Row Count",
            "New Record",
            each
            let
                varCol1 = [Column1],
                varCol4 = [Column4],
                varAllRows = [All Rows]
            in

                if [Row Count] >= 2 then [All Rows]
                else 
                    Table.Combine(
                        {
                            varAllRows, 
                            Table.FromRecords(
                                {
                                    [Column1 = varCol1, Column2 = "Name", Column4 = varCol4]
                                }
                            )
                        }
                    )
                ),
    #"Removed Other Columns" = Table.SelectColumns(#"New Row",{"New Record"}),
    #"Expanded New Record" = Table.ExpandTableColumn(#"Removed Other Columns", "New Record", {"Column1", "Column2", "Column3", "Column4"}, {"Column1", "Column2", "Column3", "Column4"})

1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[amitchandak]

@Anonymous , You can create a table using enter data. And append that with first table

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-enter-data-directly-into-desktop

https://radacad.com/append-vs-merge-in-power-bi-and-power-query