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.
Hello dear PBI Community,
I'm struggling with the following transformation..
I have two Tables, The one on top contains the Values I'm trying to get onto the second Table ( column1, Group 0, Group 1 ), this table doesn't get updated.
For each row in the second Table below, for the "Group 0", the values need to be compared to the "Group" &"Number 0" column ( Yellow Table ), and replace it with its corresponding numerical value. Same for "Group 1" / "Number 1", to be at the end able to see the same end result table
What is the best optimal way to achieve this transformation, knowing that my ( Column1, Group 0, Group 1 ) Table, is being updated with new rows on a weekly basis.
Thank you all for the help
Solved! Go to Solution.
Hi, @Vrykolakas ;
Try it.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkktLsnMS1fSUXICYkelWB1kMWcgRhMCq0QVcsQi5gLGmOqw2eCsFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, #"Group 0" = _t, #"Group 1" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Group 0", type text}, {"Group 1", type text}}),
Custom1 = let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkktLsnMS1fSUXICYkelWB1kMWcgRhMCq0QVcsQi5gLGmOqw2eCsFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, #"Group 0" = _t, #"Group 1" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Group 0", type text}, {"Group 1", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {List.Zip({#"Table1"[Group],#"Table1"[Number 0]}),List.Zip({#"Table1"[Group],#"Table1"[Number 1]})}
),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each List.ReplaceMatchingItems({[Group 0]},[Custom]{0}){0}
),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each List.ReplaceMatchingItems({[Group 1]},[Custom]{1}){0}
)
in
#"Added Custom2",
#"Removed Columns" = Table.RemoveColumns(Custom1,{"Group 0", "Group 1", "Custom"})
in
#"Removed Columns"
The final show:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Vrykolakas ;
Try it.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkktLsnMS1fSUXICYkelWB1kMWcgRhMCq0QVcsQi5gLGmOqw2eCsFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, #"Group 0" = _t, #"Group 1" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Group 0", type text}, {"Group 1", type text}}),
Custom1 = let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkktLsnMS1fSUXICYkelWB1kMWcgRhMCq0QVcsQi5gLGmOqw2eCsFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, #"Group 0" = _t, #"Group 1" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Group 0", type text}, {"Group 1", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {List.Zip({#"Table1"[Group],#"Table1"[Number 0]}),List.Zip({#"Table1"[Group],#"Table1"[Number 1]})}
),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each List.ReplaceMatchingItems({[Group 0]},[Custom]{0}){0}
),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each List.ReplaceMatchingItems({[Group 1]},[Custom]{1}){0}
)
in
#"Added Custom2",
#"Removed Columns" = Table.RemoveColumns(Custom1,{"Group 0", "Group 1", "Custom"})
in
#"Removed Columns"
The final show:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
=let a=Table.Buffer(Table1) in #table(Table.ColumnNames(Table2),Table.ToList(Table2,each List.Transform(List.Positions(_),(x)=>if x=0 then _{0} else Record.FieldOrDefault(a{[Group=_{x}]}?,"Number"&Text.From(x),null))))
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
10 | |
8 | |
7 | |
6 | |
6 |