Forum Discussion
Filling blank values in a dataset based on a shared value on a nother column
Hi everyone
I have a dataset where I have invoices with associated company names and also an associated Salesforce ID. However, some SFIDs in this fact table are blank for some reason I'm not going to dive into
What I want to do is: since the name is always the same, I want to fill the blank values in my dataset with the SFID associated with the same name
It's going to be something like this. Initial situation:
INVOICE COMPANY SFID
1 ABC 123
2 ABC null
3 ABC null
Desired result:
INVOICE COMPANY SFID
1 ABC 123
2 ABC 123
3 ABC 123
I'm sure there is a way to do this easily in M. How can I do it? Thanks
Hi Anonymous ,
We can add a custom column then remove the origin one to meet your requirement:
let c = [COMPANY] in if [SFID] = null then Table.Max(Table.SelectRows(#"Changed Type",each [COMPANY]=c),{"SFID"})[SFID] else [SFID]All the queries are here:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJ0cgaShkbGSrE60UpGcBEw1xiVawJkODm7AEkTUzOwiClcBMw1AzJc3dzB3FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [INVOICE = _t, COMPANY = _t, SFID = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"INVOICE", Int64.Type}, {"COMPANY", type text}, {"SFID", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "SFID_2", each let c = [COMPANY] in if [SFID] = null then Table.Max(Table.SelectRows(#"Changed Type",each [COMPANY]=c),{"SFID"})[SFID] else [SFID]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"SFID"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"SFID_2", "SFID"}}) in #"Renamed Columns"
Best regards,
7 Replies
In case anyone is interested, I have found a placeholder solution that involves some steps. You need to:
-Duplicate the query-Remove all columns besides the column with the shared value and the column to fill (in my case, Company and SFID)
-Remove blanks from the column you need to fill (in my case, SFID)-Remove duplicates from the column with the shared values (in my case, Company)
-After that, you should have a query where there is a unique combination of Company and SFID
-Merge this query on the original query based on the SHARED value column (Company)
-Expand the other column (SFID)
Done: all client names now have the same SFID value, also when there was blanks. Now delete the old column and use the new one
I am pretty sure there is a way to do this with a single function in M but it's still a good placeholder
v-lid-msftCommunity Support
Hi Anonymous ,
We can add a custom column then remove the origin one to meet your requirement:
let c = [COMPANY] in if [SFID] = null then Table.Max(Table.SelectRows(#"Changed Type",each [COMPANY]=c),{"SFID"})[SFID] else [SFID]All the queries are here:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJ0cgaShkbGSrE60UpGcBEw1xiVawJkODm7AEkTUzOwiClcBMw1AzJc3dzB3FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [INVOICE = _t, COMPANY = _t, SFID = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"INVOICE", Int64.Type}, {"COMPANY", type text}, {"SFID", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "SFID_2", each let c = [COMPANY] in if [SFID] = null then Table.Max(Table.SelectRows(#"Changed Type",each [COMPANY]=c),{"SFID"})[SFID] else [SFID]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"SFID"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"SFID_2", "SFID"}}) in #"Renamed Columns"
Best regards,Is there any reason this script would execute slowly??
Have inserted and coverted the names into my query which has about 1500 (unique) IDs in the [COMPANY] column with approximately 2700 rows in total.
Has been running now for way over 30 minutes in an overall script that took maybe 10 seconds previously.
Puzzled !