Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Help converting IFERROR Excel formulas to PowerQuery M

I have a dataset in Excel formatted as a table. I need to add 4 new columns to this table to test some logic. I have the following four Excel formulas in blue font, which I'd like to translate into M...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous 

     

    I am not asking you to post anything sensitive, you can't even give some dummy data like this? All the columns you mentioned are in the same table? I don't see why you need all 4 columns...maybe my dummy data can't represent your table

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRU0lECYSOlWJ1oJSMjMBPGNTYGMiE4NhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Intake id" = _t, ID = _t, #"dataT_PMT.Document Id" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "column1", each if List.Contains(Source[dataT_PMT.Document Id],[ID]) then "Yes" else "No"),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "column2", each if [column1]="Yes" then [Intake id] else ""),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "column3", each if [column1]="No" then [Intake id] else ""),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "column4", each if List.Contains(#"Added Custom2"[column2],[column3]) then "Yes" else "No")
in
    #"Added Custom3"

     

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Anonymous 

 

Can you provide some sample data in a format which we can copy? try...otherwise... is the structure, but it depends on what you want, provide some data then we can see if you need it

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Vera_33,

Sorry strict company regulations prohibit me from posting any thing other than what I am able to type into the forum. Can't even upload a JPEG or provide link to other cloud storage; eg., Box, DropBox etc. (not accessible).

Thanks,

Steve

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous 

     

    I am not asking you to post anything sensitive, you can't even give some dummy data like this? All the columns you mentioned are in the same table? I don't see why you need all 4 columns...maybe my dummy data can't represent your table

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRU0lECYSOlWJ1oJSMjMBPGNTYGMiE4NhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Intake id" = _t, ID = _t, #"dataT_PMT.Document Id" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "column1", each if List.Contains(Source[dataT_PMT.Document Id],[ID]) then "Yes" else "No"),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "column2", each if [column1]="Yes" then [Intake id] else ""),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "column3", each if [column1]="No" then [Intake id] else ""),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "column4", each if List.Contains(#"Added Custom2"[column2],[column3]) then "Yes" else "No")
in
    #"Added Custom3"