cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Adding rows

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

 

Column1Column2Column3Column4
ADEFAULTX101
ADEFAULTX102
ANAMEX101

 

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

 

Column1Column2Column3Column4
ADEFAULTX101
ADEFAULTX102
ANAMEX101
ANAMEnull

101

 

I hope that I stated everything clearly.

 

Thanks in advance,

Krišjānis

 

5 REPLIES 5
Highlighted
Super User IX
Super User IX

Re: Adding rows

@Krisjaanis , 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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Super User VI
Super User VI

Re: Adding rows

Hi @Krisjaanis ,

 

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

edhans_1-1596729662775.png

Into this:

edhans_2-1596729692383.png

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.

 

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Highlighted
Responsive Resident
Responsive Resident

Re: Adding rows

@Krisjaanis  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

Highlighted
Solution Supplier
Solution Supplier

Re: Adding rows

@Krisjaanis 

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
Highlighted
Memorable Member
Memorable Member

Re: Adding rows

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"

 

 

 

image.png

 

image.png

 

then select the DEFAULT and NAME columns

image.png

and apply unpivots columns (1° item)

image.png

 

Helpful resources

Announcements
Ignite

Microsoft Ignite

This will be a conference that you do not want to miss!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Don't miss the Power BI Dev Camp this week!

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors