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, F...
  • mahoneypat's avatar
    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