Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
@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
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)
@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
Hi @Anonymous ,
See if the M code below is what you need. It turns this:
Into this:
It does this by doing the following:
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@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