The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I have a Table with the following structure
Title | Entry | Data |
A | 10 | MasterData 1 |
A | 23 | SpecialData |
A | 54 | null |
B | 10 | MasterData 2 |
B | 34 | null |
B | 576 | null |
C | 10 | null |
C | 21312 | null |
What i want to achieve in Power Query M is that for some entries in the table i have "MasterData" which is always under Entry "10" for the corresponding Title, some may also have SpecialData which i don´t want to touch...
The Table i would want to have would be like this:
Title | Entry | Data |
A | 10 | MasterData 1 |
A | 23 | SpecialData |
A | 54 | MasterData 1 |
B | 10 | MasterData 2 |
B | 34 | MasterData 2 |
B | 576 | MasterData 2 |
C | 10 | null |
C | 21312 | null |
So i would need to somehow reference always the Data from Entry "10" for all null values of the same title data. It could also be in a new added column if that´s easier. Filldown is not helping in this case either unfortunately because i cannot restrict it to the same Title. I just found a working formula replacing the null value with an value of the same row... how can i shift that reference to always return the corresponding Title "10" Entry if there is one?
= Table.AddColumn(#"xxx", "Data", each if [Data] = null then [Entry] else [Data], type text)
This "[Entry]" i would need to point to the corresponding 10 entry of the same title...
Any hint how to do that is very appreciated! Thanks!
Solved! Go to Solution.
NewStep=Table.Combine(Table.Group(PreviousStepName,"Title",{"n",each Table.ReplaceValue(_,"","",(x,y,z)=>x??_{[Entry=10]}?[Data],{"Data"})})[n])
NewStep=Table.Combine(Table.Group(PreviousStepName,"Title",{"n",each Table.ReplaceValue(_,"","",(x,y,z)=>x??_{[Entry=10]}?[Data],{"Data"})})[n])
Thanks! This is working as I need it, I don´t understand it yet but I´ll go through it step by step till i do.
Hi @Thomas_DE,
I think this does what you are after. Please try.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0ABK+icUlqUUuiSWJCoZKsToQGSNjIBFckJqcmZgDkoJLmJoAibzSnBywiBMWQ4zgMsYYak3NzZCFnGHaUUSMDI0NjeCCsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Title = _t, Entry = _t, Data = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Title", type text}, {"Entry", Int64.Type}, {"Data", type text}}),
fTransform = (t as table) as table =>
let
Transform = List.Skip(List.Accumulate(Table.ToRecords(t), {{null, {}}}, (a, n)=> a & (if n[Entry] = 10 then {{n[Data], n }} else {{List.Last(a){0}, if n[Data]="null" then Record.TransformFields(n, {{"Data", (x)=> List.Last(a){0}}}) else n }}))),
Output = Table.FromRecords(List.Zip(Transform){1}, Value.Type(t))
in
Output,
next = #"Changed Type",
#"Grouped Rows" = Table.Combine(Table.Group(next, {"Title"}, {{"Count", fTransform}})[Count])
in #"Grouped Rows"
Kind regards,
John
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.
User | Count |
---|---|
38 | |
23 | |
23 | |
21 | |
16 |