Power Query - Data enrichment ibased on existing information

Power Query M Code Showcase
1

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)
PQ1

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

image
image1150×280 21.3 KB

I hope this is helpful

3

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

4

image
image776×157 12.4 KB

5

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

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.

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

I hope this is helpful

1 Like
7

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

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!

9

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

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"}
)
11

excellent

Thank you