Forum Discussion
Anonymous
5 years agoNot applicable
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, F...
- 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
amitchandak
5 years agoSuper User
Anonymous , While creating a connection, In advance option you can give a SQL