How can I get this table from a SQL server?

[UsePowerBI]

Hello

I want to get a table from a SQL server in this format:

TableName, FieldName1, DistinctValue1

TableName, FieldName1, DistinctValue2

TableName, FieldName2, DistinctValue1

TableName, FieldName2, DistinctValue2

TableName, FieldName2, DistinctValue3

etc

I cannot do it via SQL so I wonder if there is any way to combine a SQL statement with PowerBI table transformations to achieve this.

Any idea?

Thanks!

[mahoneypat · Accepted solution]

Here is an example of how to do this with the AdventureWorks database.  I tried it out on my PC and it worked.  Just change the name of the server and database in this code.  Paste this M code into a blank query.

let
    Source = Sql.Databases("localhost"),
    AdventureWorksDW1 = Source{[Name="AdventureWorksDW2019"]}[Data],
    #"Removed Other Columns" = Table.SelectColumns(AdventureWorksDW1,{"Name", "Data"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each Table.Distinct(Table.UnpivotOtherColumns(Table.AddIndexColumn(Table.Distinct([Data]),"Index", 1), {"Index"}, "Attribute", "Value"))),
    #"Removed Other Columns1" = Table.SelectColumns(#"Added Custom",{"Name", "Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns1", "Custom", {"Attribute", "Value"}, {"Attribute", "Value"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Custom",{{"Attribute", type text}, {"Value", type text}})
in
    #"Changed Type"

Regards,

Pat

[amitchandak]

@UsePowerBI , While creating a connection, In advance option you can give a SQL