it is possible to make a custom column if statement that calculates values of certain cells in another column in power query ?

[bleeet]

Sorry guys

This is hard for me to explain in the title but I asking if it's possible to mimic this excel if statement formula

=IF(OR(AND(J2<>J1, T2<>0), AND(J2=J1, AND(T2<>0,T1=0))),1,"")

in a custom column in power query? If so how can I do it?


thanks

 

[anvg]

Hi
Let your table is Table1 and its columns as J1 and T1

let
    source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    typed = Table.TransformColumnTypes(source,{{"J1", Int64.Type}, {"T1", Int64.Type}}),
    indexed = Table.AddIndexColumn(typed, "id", 1),
    renamed = Table.RenameColumns(typed, { {"J1", "J2"}, {"T1", "T2"} }),
    indexed2 = Table.AddIndexColumn(renamed, "id2", 0),
    joined = Table.Join(indexed, {"id"}, indexed2, {"id2"}),
    calced = Table.AddColumn(joined, "calced", each if [J1] <> [J2] and [T2] <> 0 or [J1] = [J2] and [T2] <> 0 and [T1] = 0 then 1 else null, type nullable number)
in
    calced[[J1], [T1], [J2], [T2], [calced]]

Regards,

 

[bleeet]

Hi
Let your table is Table1 and its columns as J1 and T1

let
    source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    typed = Table.TransformColumnTypes(source,{{"J1", Int64.Type}, {"T1", Int64.Type}}),
    indexed = Table.AddIndexColumn(typed, "id", 1),
    renamed = Table.RenameColumns(typed, { {"J1", "J2"}, {"T1", "T2"} }),
    indexed2 = Table.AddIndexColumn(renamed, "id2", 0),
    joined = Table.Join(indexed, {"id"}, indexed2, {"id2"}),
    calced = Table.AddColumn(joined, "calced", each if [J1] <> [J2] and [T2] <> 0 or [J1] = [J2] and [T2] <> 0 and [T1] = 0 then 1 else null, type nullable number)
in
    calced[[J1], [T1], [J2], [T2], [calced]]

Regards,

yikes not working for me

I think I can simplify my request how can I flag each name in a table as a "1"

for example my table sort of like this but with more data columns in between these

100	data	joe	dee
100	data	joe	dee
500	data	sally	woo
300	data	smith	kay
300	data	smith	kay

<tbody>
</tbody>

I want to do a head count by marking "1" in a another column beside each name. the name might be mentioned in a few rows under it but I don't want those rows marked as "1" only the first row where the name is mentioned like this.

100	data	joe	dee	1
100	data	joe	dee
500	data	sally	woo	1
300	data	smith	kay	1
300	data	smith	kay

<tbody>
</tbody>

I hope this makes sense.


thanks

 

[anvg]

Hi
No problem. Let colum names of Table1 are F1, F2, F3, F4.

let
    source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    typed = Table.TransformColumnTypes(source,{{"F1", Int64.Type}, {"F2", type text}, {"F3", type text}, {"F4", type text}}),
    grouped = Table.Group(typed, {"F1", "F2", "F3", "F4"}, {{"group", each Table.AddIndexColumn(_, "id", 1), type table}}),
    expanded = Table.ExpandTableColumn(grouped[[group]], "group", {"F1", "F2", "F3", "F4", "id"}),
    return = Table.TransformColumns(expanded, { {"id", each if _ = 1 then 1 else null, type nullable number}})
in
    return

Regards,

 

[FDibbins]

Try this...

	A​	B​	C​	D​	E​
9​	100	data	joe	dee	1
10​	100	data	joe	dee
11​	500	data	sally	woo	1
12​	300	data	smith	kay	1
13​	300	data	smith	kay

E9=IF(COUNTIFS($B$9:B9,B9,$C$9:C9,C9,$D$9:D9,D9)>1,"",1)
copied down

 

[anvg]

Hi, FDibbins!
Do you have any test of your formula in the Power Query? It is a branch about Power BI

 

[FDibbins]

Hi, FDibbins!
Do you have any test of your formula in the Power Query? It is a branch about Power BI

No, that is just a regular formula that will work with a range of data

 

[bleeet]

Hi
No problem. Let colum names of Table1 are F1, F2, F3, F4.

let
    source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    typed = Table.TransformColumnTypes(source,{{"F1", Int64.Type}, {"F2", type text}, {"F3", type text}, {"F4", type text}}),
    grouped = Table.Group(typed, {"F1", "F2", "F3", "F4"}, {{"group", each Table.AddIndexColumn(_, "id", 1), type table}}),
    expanded = Table.ExpandTableColumn(grouped[[group]], "group", {"F1", "F2", "F3", "F4", "id"}),
    return = Table.TransformColumns(expanded, { {"id", each if _ = 1 then 1 else null, type nullable number}})
in
    return

Regards,

Hi Anvg

sorry I am new to power query and it seems I am probably putting the code in the wrong place. Where do I put it? do I make a new table?