Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi,
I have a table like this. I want to fill the blank cells based on values in other column. I have achived this through DAX and Fill option in Power Query. Please help me in achieving this through creating Custom Column. I have Columns A, B, and C and i want custom Column like D. Thanks!
Solved! Go to Solution.
And here's a different approach, adding the custom column within the Table.Group function:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0MjI2VtJRcgTi0GClWB0kMScsYs5AjBAxgep0DPZ0RBV1QhYF6TUxRbUFLuYEMxEu4owh4oLkEoj5LnB3QPhBQOzq6+qILBYMVhMLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CLM_ID = _t, Activity = _t, State = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"CLM_ID", Int64.Type}, {"Activity", type text}, {"State", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"CLM_ID"}, {
{"Expected Insured State", (t)=>
Table.AddColumn(t,"Expected Insured State",
each List.Select(t[State], each Text.Length(Text.Trim(_))>0){0}, type text),
type table[CLM_ID=Int64.Type, Activity=text, State=text, Expected Insured State=text]}}),
#"Expanded Expected Insured State" = Table.ExpandTableColumn(#"Grouped Rows", "Expected Insured State",
{"Activity", "State", "Expected Insured State"})
in
#"Expanded Expected Insured State"
Source
Results
And here's a different approach, adding the custom column within the Table.Group function:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0MjI2VtJRcgTi0GClWB0kMScsYs5AjBAxgep0DPZ0RBV1QhYF6TUxRbUFLuYEMxEu4owh4oLkEoj5LnB3QPhBQOzq6+qILBYMVhMLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CLM_ID = _t, Activity = _t, State = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"CLM_ID", Int64.Type}, {"Activity", type text}, {"State", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"CLM_ID"}, {
{"Expected Insured State", (t)=>
Table.AddColumn(t,"Expected Insured State",
each List.Select(t[State], each Text.Length(Text.Trim(_))>0){0}, type text),
type table[CLM_ID=Int64.Type, Activity=text, State=text, Expected Insured State=text]}}),
#"Expanded Expected Insured State" = Table.ExpandTableColumn(#"Grouped Rows", "Expected Insured State",
{"Activity", "State", "Expected Insured State"})
in
#"Expanded Expected Insured State"
Source
Results
Hi @ManojG, why via Custom Column?
Before
After
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0MjI2VtJRcgTi0GClWB0kMScsYs5AjBAxgep0DPZ0RBV1QhYF6TUxRbUFLuYEMxEu4owh4oLkEoj5LnB3QPhBQOzq6+qILBYMVhMLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CLM_ID = _t, Activity = _t, State = _t]),
// This step can be deleted when applying qeury on real data.
ReplaceBlankToNull = Table.TransformColumns(Source, {}, each if _ = "" then null else _),
GroupedRows = Table.Group(ReplaceBlankToNull, {"CLM_ID"}, {{"All", each Table.FillUp(Table.FillDown(_, {"State"}), {"State"}), type table}}),
CombinedAll = Table.Combine(GroupedRows[All])
in
CombinedAll
v2 - Added new column:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0MjI2VtJRcgTi0GClWB0kMScsYs5AjBAxgep0DPZ0RBV1QhYF6TUxRbUFLuYEMxEu4owh4oLkEoj5LnB3QPhBQOzq6+qILBYMVhMLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CLM_ID = _t, Activity = _t, State = _t]),
DuplicatedColumn = Table.DuplicateColumn(Source, "State", "Expected Insured State"),
// This step can be deleted when applying qeury on real data.
ReplaceBlankToNull = Table.TransformColumns(DuplicatedColumn, {}, each if _ = "" then null else _),
GroupedRows = Table.Group(ReplaceBlankToNull, {"CLM_ID"}, {{"All", each Table.FillUp(Table.FillDown(_, {"Expected Insured State"}), {"Expected Insured State"}), type table}}),
CombinedAll = Table.Combine(GroupedRows[All])
in
CombinedAll
Check out the July 2025 Power BI update to learn about new features.