Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have the following situation.
Column A | Column B |
AAA | |
AAA | |
CCC | |
BBB | |
DDD | |
CCC | |
CCC | deactive |
DDD | deactive |
In Column A are different values. In column B one value "deactive" is set. I am searching for a formula that now putting the value in Column C "deactive" in every row were Column A is null (see table below as a solution).
Column A | Column B | Column C |
AAA | ||
AAA | ||
CCC | deactive | |
BBB | ||
DDD | deactive | |
CCC | deactive | |
CCC | deactive | deactive |
DDD | deactive | deactive |
I hope someone can help me out 🙂
Cheers,
Kai
Solved! Go to Solution.
@Kaitra ,
Ah yes, I see.
In Power Query, go to New Source>Blank Query then in Advanced Editor paste my code over the default code. You can then follow the steps I took to complete this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnR0VNJRUlCK1UFlOzs7w9lOTk5wtouLC1Y1EHZKamJySWZZKpJShFAsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column A" = _t, #"Column B" = _t]),
chgTypes = Table.TransformColumnTypes(Source,{{"Column A", type text}, {"Column B", type text}}),
groupColumnA = Table.Group(chgTypes, {"Column A"}, {{"data", each _, type table [Column A=nullable text, Column B=nullable text]}}),
addMaxColumnB = Table.AddColumn(groupColumnA, "maxColumnB", each Table.Max([data], "Column B")),
expandMaxColumnB = Table.ExpandRecordColumn(addMaxColumnB, "maxColumnB", {"Column B"}, {"Column B"}),
expandData = Table.ExpandTableColumn(expandMaxColumnB, "data", {"Column B"}, {"Column B.1"})
in
expandData
SUMMARY:
1) Group table on [Column A] and keep All Rows as [data]
2) Get max value of column B from nested table and expand record
3) Expand [data] again to reinstate original data
This gives me the following output:
Pete
Proud to be a Datanaut!
I had something written before I saw @BA_Pete's answer. Please accept that one if it works for you but I thought I might as well post a slightly different alternative since it's already written.
You could group by Column A and define the max over Column B as Column C and then merge this back to the original table.
Group:
Merge and Expand:
Full sample M code you can paste into your Advanced Editor:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnR0VNJRUlCK1UFlOzs7w9lOTk5wtouLC1Y1EHZKamJySWZZKpJShFAsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column A" = _t, #"Column B" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column A", type text}, {"Column B", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Column A"}, {{"Column C", each List.Max([Column B]), type nullable text}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Column A"}, #"Grouped Rows", {"Column A"}, "Grouped Rows", JoinKind.LeftOuter),
#"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Column C"}, {"Column C"})
in
#"Expanded Grouped Rows"
Hi @ anyone who might be interested.
Just for fun, I combined my solution and @AlexisOlson 's solution into a single step.
The following should be added as a custom step:
Table.Group(
previousStep,
{"Column A"},
{
{"Column C", each try
Text.Split(
Text.Trim(
Text.Repeat(
List.Max([Column B]) & " ",
Table.RowCount(_)
)
), " "
) otherwise null}
}
)
Expand the resulting list and, Voila!
Full M code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnR0VNJRUlCK1YGxwUxnZ2e4sJOTE5zt4uICZyOrgbBTUhOTSzLLUpGUIoRiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column A" = _t, #"Column B" = _t]),
repBlankNull = Table.ReplaceValue(Source," ",null,Replacer.ReplaceValue,{"Column B"}),
groupColumnA =
Table.Group(
repBlankNull,
{"Column A"},
{
{"Column C", each try
Text.Split(
Text.Trim(
Text.Repeat(
List.Max([Column B]) & " ",
Table.RowCount(_)
)
), " "
) otherwise null}
}
),
expandColumnC = Table.ExpandListColumn(groupColumnA, "Column C")
in
expandColumnC
Pete
Proud to be a Datanaut!
This is a fun combo. 🙂
Not ideal if there are additional columns in the query though.
Thanks a lot both of you. I really appreciate the quick and helpful support here in the forum!!!
Like @AlexisOlson mentioned, I had additional columns which I needed (forget to mention in the topic). So the solution from both of you is working pretty well 🙂
I had something written before I saw @BA_Pete's answer. Please accept that one if it works for you but I thought I might as well post a slightly different alternative since it's already written.
You could group by Column A and define the max over Column B as Column C and then merge this back to the original table.
Group:
Merge and Expand:
Full sample M code you can paste into your Advanced Editor:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnR0VNJRUlCK1UFlOzs7w9lOTk5wtouLC1Y1EHZKamJySWZZKpJShFAsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column A" = _t, #"Column B" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column A", type text}, {"Column B", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Column A"}, {{"Column C", each List.Max([Column B]), type nullable text}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Column A"}, #"Grouped Rows", {"Column A"}, "Grouped Rows", JoinKind.LeftOuter),
#"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Column C"}, {"Column C"})
in
#"Expanded Grouped Rows"
Hi @Kaitra ,
I'm not sure I understand: you have values in column C where column A isn't null.
Have I misunderstood your requirement?
Pete
Proud to be a Datanaut!
Hi @BA_Pete
the source table is the first one. I'd like to have column C as a result. Column C doesn't exist in the current table. I needs to be created. So the situation is, that in Column B a value is set only for one entry, I'd like to have it for every matching entry. In my example it would be "CCC" and "DDD".
@Kaitra ,
Ah yes, I see.
In Power Query, go to New Source>Blank Query then in Advanced Editor paste my code over the default code. You can then follow the steps I took to complete this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnR0VNJRUlCK1UFlOzs7w9lOTk5wtouLC1Y1EHZKamJySWZZKpJShFAsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column A" = _t, #"Column B" = _t]),
chgTypes = Table.TransformColumnTypes(Source,{{"Column A", type text}, {"Column B", type text}}),
groupColumnA = Table.Group(chgTypes, {"Column A"}, {{"data", each _, type table [Column A=nullable text, Column B=nullable text]}}),
addMaxColumnB = Table.AddColumn(groupColumnA, "maxColumnB", each Table.Max([data], "Column B")),
expandMaxColumnB = Table.ExpandRecordColumn(addMaxColumnB, "maxColumnB", {"Column B"}, {"Column B"}),
expandData = Table.ExpandTableColumn(expandMaxColumnB, "data", {"Column B"}, {"Column B.1"})
in
expandData
SUMMARY:
1) Group table on [Column A] and keep All Rows as [data]
2) Get max value of column B from nested table and expand record
3) Expand [data] again to reinstate original data
This gives me the following output:
Pete
Proud to be a Datanaut!
Thanks a lot, that helped me a lot!!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.