Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How can I get this table from a SQL server?

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!

Need Help
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    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

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

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

     

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    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