Combine Multiple Columns of data into one column

[Anonymous]

I have one table with 16 columns of true/false information in it.  I am currently doing the following:

Creating columns:

No Prob Found = IF([noproblem] = TRUE(), "NO PROBLEM FOUND" , "No Issue")

Impact2 = IF(OR([crushed] = TRUE(), [impact] = TRUE()), "Impact or Crushed", "No Issue")

After the columns are combined looking to see what data in Excel to find over lap and then creating a extra column for those.  then using this switch statement to combine into one column:

Combined Issues = SWITCH(TRUE(),

AND([Comp Issue] <> "No Issue", [Normal Wear] <> "No Issue"), "Component Issue AND Normal Wear",

AND([Normal Wear] <> "No Issue", [Heat Stress] <> "No Issue"), "Normal Wear AND Heat Stress or Preventive Maintenance",

AND([Comp Issue] <> "No Issue", [Conn Problems] <> "No Issue"), "Component Issue AND Connection Problems",

AND([Normal Wear] <> "No Issue", [conn problems] <> "No Issue"), "Normal Wear AND Connection Problems",

[Comp Issue] <> "No Issue", "Component Missing or Component Failure",

[No Prob Found] <> "No Issue", "No Problem Found",

[Normal Wear] <> "No Issue", "Normal Wear",

[IMPACT2] <> "No Issue", "Impact or Crushed",

[Heat Stress] <> "No Issue", "Heat Stress or Exposed to Moisture",

[Over Volt] <> "No Issue", "Over Voltage or Over Current",

[Conn Problems] <> "No Issue", "Connection Problems",

[Modifications] <> "No Issue", "Modifications or Configuration Changes",

[Other Issues] <> "No Issue", "Other Problem Found",

[Comp Issue w AR] <> "No Issue", "Component Issue W/ Advance Replacement",

[COEandADVR] <> "No Issue", "Cervis Order Error and Advance Replacement",

[Advance Replace] <> "No Issue", "Advance Replacement",

[CervisOrder] <> "No Issue", "Cervis Order Error",

"No Issue Recorded")

TO see this result in visual:

 Is there an easier way to get this result?

THanks

Nhtraven

[BhaveshPatel]

Hi @Anonymous

You can use Conditional Column in Query Editor with some transformations that require few clicks.

[Anonymous]

So in looking up the conditional columns (I am new to Power Query)  It is the same as a calculated column in power BI or powerpivot?   Except it has a lot more functionality than calculated column, to reshape and combine all of the columns.   And alot of it with conditional column is pick and click and power query creates the code to make it work? 

I will have to play with this and watch more videos with explanation of how it works. 

Thanks - you opened a whole new realm of learning for me!  I know that power Query (in power BI desktop) is incredibile - i have been using it for splitting columns and transposing, unpivoting. pivoting - but this is a totally new area for me. 

THanks!

Nhtraven

[v-ljerr-msft]

Hi @Anonymous,

I agree with BhaveshPatel that it's better to use Query Editor to create the combined column in this scenario.

We usually use Power Query (M) to query data sources, clean and load data first. Then use DAX to analyze the data in Power Pivot. Use the right tool to do the right job will keep us more efficient.

Regards

[BhaveshPatel]

Yes.It is incredibly powerful but it runs on " M" and You should learn M to harness its complete power and transform your data  by writing custom functions, parameters and many other things. However, The tab options in PowerQuery are so rich and useful that it does most of the task. 

DAX is a language of PowerBI calculation engine, so called " Vertipaq" or "PowerPivot". It is much faster and can aggregate large amounts of data.