append multiple columns to 2 columns in a new table

[LucasPHFConsult]

Hey, can somebody help me out this this query?

I have a table with multiple Org ID columns and I want to append it to one column.  That is done with the query below, but I dont know how to pull in the description.  I did a lookup to a single Org ID in the past I am getting errors like multiple values when a singe is expected, or the column I lookup does not have th value and needs some type of nested if.

Any help would be greatly appreciated!

WIP query:

Alt_Org_ID =

VAR vTable1 =

CALCULATETABLE(VALUES('CRM - People'[organization_id_1]))

VAR vTable2 = VALUES('CRM - People'[organization_id_2])

VAR vTable3 = VALUES('CRM - People'[organization_id_3])

VAR vTable4 = VALUES('CRM - People'[organization_id_4])

VAR vTable5 = VALUES('CRM - People'[organization_id_5])

VAR vTable6 = VALUES('CRM - People'[organization_id_6])

VAR vTable7 = VALUES('CRM - People'[organization_id_7])

VAR vTable8 = VALUES('CRM - People'[organization_id_8])

VAR vTable9 = VALUES('CRM - People'[organization_id_9])

VAR vTable10 = VALUES('CRM - People'[organization_id_10])

VAR vTable11 = VALUES('CRM - People'[organization_1])

VAR vTable12 = VALUES('CRM - People'[organization_2])

VAR vTable13 = VALUES('CRM - People'[organization_3])

VAR vTable14 = VALUES('CRM - People'[organization_4])

VAR vTable15 = VALUES('CRM - People'[organization_5])

VAR vTable16 = VALUES('CRM - People'[organization_6])

VAR vTable17 = VALUES('CRM - People'[organization_7])

VAR vTable18 = VALUES('CRM - People'[organization_8])

VAR vTable19 = VALUES('CRM - People'[organization_9])

VAR vTable20 = VALUES('CRM - People'[organization_10])

VAR vEnd1 = UNION(vTable1,vTable2,vTable3,vTable4,vTable5,vTable6,vTable7,vTable8,vTable9,vTable10)

VAR vEnd2 = UNION(vTable11,vTable12,vTable13,vTable14,vTable15,vTable16,vTable17,vTable18,vTable19,vTable20)

RETURN vEnd1

Different Column:

Org_Desc =

VAR vTable11 = VALUES('CRM - People'[organization_1])

VAR vTable12 = VALUES('CRM - People'[organization_2])

VAR vTable13 = VALUES('CRM - People'[organization_3])

VAR vTable14 = VALUES('CRM - People'[organization_4])

VAR vTable15 = VALUES('CRM - People'[organization_5])

VAR vTable16 = VALUES('CRM - People'[organization_6])

VAR vTable17 = VALUES('CRM - People'[organization_7])

VAR vTable18 = VALUES('CRM - People'[organization_8])

VAR vTable19 = VALUES('CRM - People'[organization_9])

VAR vTable20 = VALUES('CRM - People'[organization_10])

RETURN

lookupvalue('CRM - People'[organization_1],'CRM - People'[organization_id_1],[organization_id_1])

[LucasPHFConsult · Accepted solution]

I figured it out...

Org ID & Description =

Distinct(

FILTER(

UNION(

SUMMARIZE('CRM - People','CRM - People'[organization_id_1],'CRM - People'[organization_1]),

SUMMARIZE('CRM - People','CRM - People'[organization_id_2],'CRM - People'[organization_2]),

SUMMARIZE('CRM - People','CRM - People'[organization_id_3],'CRM - People'[organization_2]),

SUMMARIZE('CRM - People','CRM - People'[organization_id_4],'CRM - People'[organization_3]),

SUMMARIZE('CRM - People','CRM - People'[organization_id_5],'CRM - People'[organization_5]),

SUMMARIZE('CRM - People','CRM - People'[organization_id_6],'CRM - People'[organization_6]),

SUMMARIZE('CRM - People','CRM - People'[organization_id_7],'CRM - People'[organization_7]),

SUMMARIZE('CRM - People','CRM - People'[organization_id_8],'CRM - People'[organization_8]),

SUMMARIZE('CRM - People','CRM - People'[organization_id_9],'CRM - People'[organization_9]),

SUMMARIZE('CRM - People','CRM - People'[organization_id_10],'CRM - People'[organization_10])),'CRM - People'[organization_id_1]<> blank()))

[Anonymous]

Hi @LucasPHFConsult ,

It seems there is a situation that one Org ID has more than one value. In that case Power BI doesn't know which value to take then it throw out the error.

It is better to share some sample data and expected result to us.

Best Regards,

Jay

[LucasPHFConsult]

You were right.  I see that now, but it should be unique.  Thank you so much for your time and input.

[LucasPHFConsult]

I assumed that it would pull in the first instance, kind of how a vlookup is coded but that makes sense.  This is the first response I have ever done on here, so dont know if pasiting a pic is the norm.  I know Power Query can transpose these but there are about a hundred column headers inbetween these that I pasted and dont know if I can ultimately get a dynamic table with 2 column headers with the Id and descriptions that way.  I thought that doing an append query from joining a bunch of virtual tables would do it, but if you have a better way, please do share.  Thanks so much!

[amitchandak]

@LucasPHFConsult , Option one we could have used Power Query. Second with summarize to bring both ID and description

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Please let me know, if Power query is not an option to be used

[LucasPHFConsult]

amiratchandak Thank you for your time as well!  All set now

[LucasPHFConsult]

Im all for whatever way is most efficient.  Im stumpped, so open to suggestions.  Thanks so much!