Hey

I want to complete missing information in some of the rows in my data table

The intention is to fill the empty cells in the Cannel column only if there is information for the value of Cannel in other rows
with the same “Source” + “C_Name”

If there is no unique information, leave it blank

Later I would like for cases where there is more than one option for Cannel to choose the option where “Amount_Spent” is the highest

I know how to do it easily in Excel and VB but I can’t do it in the query

I would really appreciate for any help how to do this
Or where to look for an example of using query objects and functions and try to learn how to adapt it to my needs

Thanks in advance for any ideas or starting points on how to deal with this

I’m sure the solution is quite simple
My_PQ_Test_New (1).xlsx (21.3 KB)

Melissa
February 16, 2024, 11:40am
2
Answer:

Hi @y70852846 ,

Welcome to the Forum.

See if this works for you, it grabs the Channel from the max Amount_Spent where Channel is not null.

```
let
Source = Excel.CurrentWorkbook(){[Name="SrcData"]}[Content],
GroupRows = Table.Group(Source, {"source", "C_Name"},
{
{"Channel", each Table.Max( Table.SelectRows( _, each [Channel] <> null), each [Amount_Spent])[Channel]?},
{"t", each _, type table [source=text, Date=datetime, Channel=nullable text, C_Name=text, Amount_Spent=nullable number, No_P=nullable number, NoSold_P=nullable number]}
}
),
ExpandFields = Table.ExpandTableColumn(GroupRows, "t", {"Date", "Amount_Spent", "No_P", "NoSold_P"}),
ReorderColumns = Table.ReorderColumns(ExpandFields,Table.ColumnNames(Source))
in
ReorderColumns
```

with this result

I hope this is helpful

Melissa:

```
let
Source = Excel.CurrentWorkbook(){[Name="SrcData"]}[Content],
GroupRows = Table.Group(Source, {"source", "C_Name"},
{
{"Channel", each Table.Max( Table.SelectRows( _, each [Channel] <> null), each [Amount_Spent])[Channel]?},
{"t", each _, type table [source=text, Date=datetime, Channel=nullable text, C_Name=text, Amount_Spent=nullable number, No_P=nullable number, NoSold_P=nullable number]}
}
),
ExpandFields = Table.ExpandTableColumn(GroupRows, "t", {"Date", "Amount_Spent", "No_P", "NoSold_P"}),
ReorderColumns = Table.ReorderColumns(ExpandFields,Table.ColumnNames(Source))
in
ReorderColumns
```

first of all thank you very much!

In case there is already a value in the Channel field, it should remain as it is

The goal is to complete missing data without changing the existing data

Trying with my limited knowledge level to understand the solution

I would be happy to try to learn how you solved it and follow step by step with each step in a new column so I can easily follow the steps

Thanks in advance

Src_G at 03/02 has value Cnl_3
i have in my data only one empty cell so the value in this cell should be Cnl_1

Melissa
February 16, 2024, 1:21pm
6
Hi @y70852846 ,

Thanks for clearing that up, see if this meets your requirement.

```
let
Source = Excel.CurrentWorkbook(){[Name="SrcData"]}[Content],
GroupRows = Table.Group(Source, {"source", "C_Name"},
{
{"ChannelSub", each Table.Max( Table.SelectRows( _, each [Channel] <> null), each [Amount_Spent])[Channel]?},
{"t", each _, type table [source=text, Date=datetime, Channel=nullable text, C_Name=text, Amount_Spent=nullable number, No_P=nullable number, NoSold_P=nullable number]}
}
),
ExpandFields = Table.ExpandTableColumn(GroupRows, "t", {"Date", "Channel", "Amount_Spent", "No_P", "NoSold_P"}, {"Date", "Channel", "Amount_Spent", "No_P", "NoSold_P"}),
ReplaceValue = Table.ReplaceValue(ExpandFields,null,each [ChannelSub],Replacer.ReplaceValue,{"Channel"}),
RemoveCols = Table.RemoveColumns(ReplaceValue,{"ChannelSub"}),
ReorderColumns = Table.ReorderColumns(RemoveCols,Table.ColumnNames(Source))
in
ReorderColumns
```

Here are the transformations I applied.

Group By on columns: source and C_Name
In the Group By dialog I added 2 columns with the operation Äll Rows
Called the first one ChannelSub and modified the expression to obtain the Channel from the max Amount_Spent where Channel is not null.
Called the second one t, to be able to return all values.
Expanded t to bring back all values
Replaced the Challel value if null, with ChannelSub
Removed the ChannelSub helper column
Restord the original column order (when not relevant, remove this step)
I hope this is helpful

1 Like

Melissa:

```
let
Source = Excel.CurrentWorkbook(){[Name="SrcData"]}[Content],
GroupRows = Table.Group(Source, {"source", "C_Name"},
{
{"ChannelSub", each Table.Max( Table.SelectRows( _, each [Channel] <> null), each [Amount_Spent])[Channel]?},
{"t", each _, type table [source=text, Date=datetime, Channel=nullable text, C_Name=text, Amount_Spent=nullable number, No_P=nullable number, NoSold_P=nullable number]}
}
),
ExpandFields = Table.ExpandTableColumn(GroupRows, "t", {"Date", "Channel", "Amount_Spent", "No_P", "NoSold_P"}, {"Date", "Channel", "Amount_Spent", "No_P", "NoSold_P"}),
ReplaceValue = Table.ReplaceValue(ExpandFields,null,each [ChannelSub],Replacer.ReplaceValue,{"Channel"}),
RemoveCols = Table.RemoveColumns(ReplaceValue,{"ChannelSub"}),
ReorderColumns = Table.ReorderColumns(RemoveCols,Table.ColumnNames(Source))
in
ReorderColumns
```

Perfect and elegant!
My_PQ_Test_OK.xlsx (31.0 KB)

I came to the same result in a much, much longer way…

additional help that I didn’t ask before
How do I change the value of source for rows where C_Name contains a certain string?
For example, any C_Name that contains “nmNew”, the corresponding source will change to Src_G no matter what the original value was

Thanks in advance

Melissa
February 16, 2024, 2:05pm
8
@y70852846 ,

By the sounds of it you can use a Replace Value step.
BTW don’t forget to mark the answer to your inquiry as solution via … (the dots)

Thanks!

y70852846:

“nmNew”

I know I need to use something like if the text contains “nmNew” then change the value in source but so far I can’t

Thank you very much for your help

I learned a lot from you today

Melissa
February 16, 2024, 2:25pm
10
@y70852846 ,

Give something like this a go:

```
Table.ReplaceValue(
PrevStepName,
each Text.Contains([C_Name], "nmNew"),
each "Src_G",
(x, y, z)=> if y then z else x,
{"C_Name"}
)
```