columns to rows in direct mode

[Anonymous]

Hi All,

I am using direct query mode. Kindly help me to build dax to get desired output table.

Thanks

Sample Data

Account Number	Line Number	Address
1001	1	AAAAAAAA
1001	2	BBBBBB
1001	3	CCCCCC
1001	4	DDDDDD
1001	5	EEEEEE

Desired O/p

Account	Address 1	Address 2	Address 3	Address 4	Address 5
1001	AAAAAAAA	BBBBBB	CCCCCC	DDDDDD	EEEEEE

[v-piga-msft]

Hi @Anonymous ,

By my tests with Direct Query, you may could try unpivot in query editor. Please refer to the M Query.

let
    Source = Sql.Database("xxxxxx", "cherry"),
    dbo_Table_8 = Source{[Schema="dbo",Item="Table_8"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(dbo_Table_8,{{"Line Number", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Account Number", "Line Number"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Custom", each [Attribute]&""&[Line Number]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Line Number", "Attribute"})
in
    #"Removed Columns"

Here is the output.

Best Regards,

Cherry

[Anonymous]

Plz consider the below screenshot

as per the screenshot u have provided where custom is exp_line_number, address is explantion, account mnumber is transaction_sys_no . 

Unable to re-create the same M-code on my file  

[Anonymous]

unable to recreate the same in my application .

From where u got line number field 

[PattemManohar]

@Anonymous  If the source is a database, I suggest to write a SQL Query using UNPIVOT while retrieving the data itself in Direct Query mode. Let me know if you are happy with that approach.

[Anonymous]

[Anonymous]

i have written a dax query which returns values for the dimension wise .. if there is no values for any particular dimension then its not showning the dimension also . so , i have written if considition on the measure using isblank but i m getting error while using this if condition . could you help how to fix it . 

If dont mind can i have ur mobile number if u r from india . 

Dimension:

Account number, trascation number 

Measure :

EXPLANATION1_R=CALCULATE(LASTNONBLANK('TRANSACTION EXPLANATIONS'[EXPLANATION],1),'TRANSACTION EXPLANATIONS'[EXPLANATION_LINE_NUMBER]=1)
EXPLANATION2_R=CALCULATE(LASTNONBLANK('TRANSACTION EXPLANATIONS'[EXPLANATION],1),'TRANSACTION EXPLANATIONS'[EXPLANATION_LINE_NUMBER]=2)
 
the above measures might have null values for those dimensions . I want replace those null values with "N/A".  

Thanks,

[Anonymous]

Thanks for respnse ..

we are strictly advice not to write any  sql stmt . so , looking forwards for any dax query help .

Thanks