Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
ziying35
Impactful Individual
Impactful Individual

@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
Not applicable

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

 

Nishantjain
Continued Contributor
Continued Contributor

@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
Super User
Super User

Hi @Anonymous ,

 

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
amitchandak
Super User
Super User

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors