Modify table

[marabeda]

Dear Community, I am new on Power BI and in particular in codig with m language. So be so kind to explain easily.

I have a table where in the column "Cliente/mandato" the names of the clients and of the sellers. The names of the seller are above the name of clients (picture1 in yellow is the name of the seller). I want to create a column where I associate the name of client to the seller on the same row, like in picture 2.

Thank you for your precious help.

Marabeda

Picture 1

Picture2

[KT_Bsmart2gethe · Accepted solution]

Hi @marabeda ,

Please see below solution based on screenshot:

1. add a custom step and paste over the code below(a) (The screenshot below shows you where to add the custom step).

(a) Code for custom step:

Table.FillDown(Table.AddColumn(#"Changed Type", "Custom", each if List.ContainsAny(Record.ToList(_), {null}) then List.First(Record.ToList(_)) else null), {"Custom"})

Translate:

1. Record.ToList(_) converts each row into a list.

2. List.ContainsAny(Record.ToList(_), {null}) search through the list at each row for null

3. if the row contains null then List.First(Record.ToList(_)) i.e. get the first value from the list  else null

4. Table.AddColumn() Add a new column

5. Table.FillDown() Fill down the value in the newly added column

Regards

KT

[v-kkf-msft · Accepted solution]

Hi @marabeda ,

Please open a blank query and paste the code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hc0xCsAgDAXQq0jmLmrVg7SbOEjJIKhDtfevSIdAhUCWHx7/ew8Nc8Zb1FhQSNjgu7B5uHLC2uf3wN5TiTVmHOl8RhJJFGxpREm14rSams6q5azmivSvSC+Ldq5op9pw2lBtOW2pdpx2EMIL", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Cliente/mandato" = _t, Cadenza = _t, Orario = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Cliente/mandato", type text}, {"Cadenza", type text}, {"Orario", type text}, {"Value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Is seller", each if List.Count(List.Select(Record.ToList(_), each  _ <> "" and _ <> null)) = 1 then [#"Cliente/mandato"] else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Fill", each [Is seller]),
    #"Filled Down" = Table.FillDown(#"Added Custom1",{"Fill"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Is seller] = null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Is seller"})
in
    #"Removed Columns"

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[KT_Bsmart2gethe · Accepted solution]

Hi @marabeda ,

Please see below solution based on screenshot:

1. add a custom step and paste over the code below(a) (The screenshot below shows you where to add the custom step).

(a) Code for custom step:

Table.FillDown(Table.AddColumn(#"Changed Type", "Custom", each if List.ContainsAny(Record.ToList(_), {null}) then List.First(Record.ToList(_)) else null), {"Custom"})

Translate:

1. Record.ToList(_) converts each row into a list.

2. List.ContainsAny(Record.ToList(_), {null}) search through the list at each row for null

3. if the row contains null then List.First(Record.ToList(_)) i.e. get the first value from the list  else null

4. Table.AddColumn() Add a new column

5. Table.FillDown() Fill down the value in the newly added column

Regards

KT

[AlB]

Hi @marabeda 

Can you share some sample data in text-tabular format, so that the data can be copied?

Then we can build a solution upon that

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.