the given datasource kind is not supported power bi - Auto ML Classification

[malcolm_dmello]

Hi Team,

I'm getting the given datasource kind is not supported power bi error after applying the classification model to the dataset.

Kindly provide the guidelines for the dataset.

Regards

Malcolm

[v-xuding-msft]

Hi @malcolm_dmello ,

To create a machine learning model in Power BI, you must first create a dataflow for the data containing the historical outcome information, which is used for training the ML model. For more details, I think the document below can help you.

Automated Machine Learning in Power BI 

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[malcolm_dmello]

section Section1;
shared HFC_Overdue_Report = let
  Source = Sql.Database(),
  Navigation = Source{[Schema = "dbo", Item = "Report"]}[Data],
  #"Removed columns" = Table.RemoveColumns(Navigation, {"Customer Name", "BranchName", "ConsolBranch", "DISTRICT_DESC", "PINCODE", "LOB", "DEAL_APPROVAR", "ASSETDESC", "LOAN_SECTOR_TYPE", "Resolution Status", "Current Bucket", "Opening Bucket", "CUSTOMER_DOB", "GUARANTOR_1_NAME"}),
  #"Removed columns 1" = Table.RemoveColumns(#"Removed columns", {"OtherChargesDesc", "LoanAccountNo", "OCCUPATION", "DEAL_SOURCE_TYPE", "MARITAL_STATUS", "GENDER", "Product", "Segment"}),
  #"Inserted conditional column" = Table.AddColumn(#"Removed columns 1", "Custom", each if [CurrentMonth OverdueFlag] = 1 then "TRUE" else "FALSE"),
  #"Kept top rows" = Table.AddColumn(#"Removed columns 1", "Custom", each if [CurrentMonth OverdueFlag] = 1 then "TRUE" else "FALSE"),
  #"Transform columns" = Table.TransformColumnTypes(#"Kept top rows", {{"Custom", type text}}),
  #"Replace errors" = Table.ReplaceErrorValues(#"Transform columns", {{"Custom", null}})
in
  #"Replace errors";
shared #"HFC Training Data" = let
  Source = HFC_Overdue_Report,
  #"Selected columns" = Table.SelectColumns(Source, {"ReportingMonth", "MOB", "CustomerNO", "PreviousMonth", "ConsolZone", "CUSTOMER_CATEGORY", "LOAN_ASSET_COST", "LOAN_IRR2", "INT_MNTHS", "EMIAmount", "TotalEMI", "CycleDay", "DateofDisbursement", "DisbursalAmount", "TOTAL_DISBURSMENT_AMOUNT", "PreviousMonth Customer Total O/S", "Customer Total O/s", "Opening Book", "Loan Total O/s", "PreviousMonth Total Overdue Balance", "Total Overdue Balance", "ADVANCE AMOUNT RECEIVED", "PreviousMonth PrincipalOverdue", "PrincipalOverdue", "PreviousMonth InterestOverdue", "InterestOverdue", "PreviousMonth ChargesOverdue", "ChargesOverdue", "PreviousMonth Pre_EMI_Overdue_Amount", "Pre_EMI_Overdue_Amount", "PreviousMonth Principal DPD", "Principal_DPD", "PreviousMonth Interest DPD", "Interest_DPD", "PreviousMonth loan Chrg DPD", "loan_Chrg_DPD", "PreviousMonth PEMI DPD", "Pre_EMI_DPD", "PreviousMonthNPAFlag", "PreviousMonth MaxDPD", "CurrentMonth MaxDPD", "PreviousMonth OverdueFlag", "CurrentMonth OverdueFlag", "ActiveFlag", "BounceCharge", "EMIDueBilledEMIs", "CurrentMonthEMI", "LPPAmount", "NoOfInstallmentDue", "OtherCharges", "PerDayLPP", "DPDBucket", "Custom"}),
  #"Removed nulls" = Table.SelectRows(#"Selected columns", each [Custom] <> null and [Custom] <> ""),
  #"Sampled input" = AI.SampleStratifiedWithHoldout("Custom", Table.RowCount(#"Removed nulls"), #"Removed nulls")
in
  #"Sampled input";
shared HFC = let
  Source = #"HFC Training Data",
  DataflowPrecalculatedSource = PowerBI.Dataflows(),
  Workspace = DataflowPrecalculatedSource{[workspaceId = "fbe83004-d2da-4dc1-a5e2-0d30ab7e618d"]}[Data],
  Dataflow = Workspace{[dataflowId = "4f64cf66-2713-4e6d-a37c-68d02bc7a1cd"]}[Data],
  Preview = Dataflow{[entity = "HFC"]}[Data],
  #"Invoked TrainClassification" = AIFunctions.Contents(){[Key = "AI.Execute"]}[Data]("AI.TrainClassificationWithTimeout", "Regular", [classificationColumnName = "Custom", data = Source, timeout = 11]),
  #"Selected training schema columns" = Table.Buffer(Table.SelectColumns(#"Invoked TrainClassification", {"TrainingId", "Model", "Stats", "GlobalExplanation", "TrainingSchema", "ClassificationColumn", "Reports", "ConfusionMatrix"})),
  ModelEntity = if (Table.First(#"Selected training schema columns")[Model]="Unavailable") = true then #"Preview" else #"Selected training schema columns"
in
  ModelEntity;
shared HFC.Score = let
  ApplyScoringFunction = (inputQuery as table, newColumn as text) => let
  MlModel = HFC,
  MlModelJson = try Text.FromBinary(Json.FromValue(MlModel{0})) otherwise "InvalidModel",
  IsDefaultResponse = if MlModelJson = "InvalidModel" then true else false,
  Source = inputQuery,
  SelectedBaseEntityColumns = {"ReportingMonth", "MOB", "CustomerNO", "PreviousMonth", "ConsolZone", "CUSTOMER_CATEGORY", "LOAN_ASSET_COST", "LOAN_IRR2", "INT_MNTHS", "EMIAmount", "TotalEMI", "CycleDay", "DateofDisbursement", "DisbursalAmount", "TOTAL_DISBURSMENT_AMOUNT", "PreviousMonth Customer Total O/S", "Customer Total O/s", "Opening Book", "Loan Total O/s", "PreviousMonth Total Overdue Balance", "Total Overdue Balance", "ADVANCE AMOUNT RECEIVED", "PreviousMonth PrincipalOverdue", "PrincipalOverdue", "PreviousMonth InterestOverdue", "InterestOverdue", "PreviousMonth ChargesOverdue", "ChargesOverdue", "PreviousMonth Pre_EMI_Overdue_Amount", "Pre_EMI_Overdue_Amount", "PreviousMonth Principal DPD", "Principal_DPD", "PreviousMonth Interest DPD", "Interest_DPD", "PreviousMonth loan Chrg DPD", "loan_Chrg_DPD", "PreviousMonth PEMI DPD", "Pre_EMI_DPD", "PreviousMonthNPAFlag", "PreviousMonth MaxDPD", "CurrentMonth MaxDPD", "PreviousMonth OverdueFlag", "CurrentMonth OverdueFlag", "ActiveFlag", "BounceCharge", "EMIDueBilledEMIs", "CurrentMonthEMI", "LPPAmount", "NoOfInstallmentDue", "OtherCharges", "PerDayLPP", "DPDBucket", "Custom"},
  InputRowCount = Table.RowCount(Source),
  InputTableType = Value.Type(Source),
  SelectedColumnsTypes = List.Transform(SelectedBaseEntityColumns, each Type.TableColumn(InputTableType, _)),
  DefaultRecord = Record.FromList({"Unavailable","Unavailable","Unavailable","Unavailable"}, type[ClassificationResult = text,ClassificationScore = text,ClassProbabilities = text,ClassificationExplanation = text]),
  DefaultTable = Table.FromRecords({DefaultRecord}),
  ExpandedDefaultTable = Table.Repeat(DefaultTable, InputRowCount),
  ScoringFunction = 
        let
            ScoringFunctionScalarType = type function (row as record) as any,
            VectorizedScoringFunction = (input as table) =>
              let
                ExpandedColumns = Table.ExpandRecordColumn(input, "row", SelectedBaseEntityColumns),
                ExpandedColumnsWithTypes = Table.TransformColumnTypes(ExpandedColumns, List.Zip({SelectedBaseEntityColumns, SelectedColumnsTypes})),
                ErrorList = List.Repeat({[Output = null]}, InputRowCount),
                Result = if MlModelJson <> "InvalidModel" then (try Table.ToRecords(AIFunctions.Contents(){[Key = "AI.Execute"]}[Data]("AI.ScoreClassification", "Vectorized", [data = ExpandedColumns, scoreParameters = MlModelJson])) otherwise ErrorList) else ErrorList
              in
                Result,
            ScalarVectorScoringFunction = Function.ScalarVector(ScoringFunctionScalarType, VectorizedScoringFunction)
        in
            ScalarVectorScoringFunction,
  CombinedTablesDefault = Table.NestedJoin(Source, {}, ExpandedDefaultTable, {}, newColumn, JoinKind.FullOuter),
  CombinedTablesAI = Table.AddColumn(Source, newColumn, each ScoringFunction(_)),
  AddScoringColumn = if IsDefaultResponse = true then CombinedTablesDefault else CombinedTablesAI,
  ExpandResultColumns = if IsDefaultResponse = true then Table.ExpandTableColumn(AddScoringColumn, newColumn, {"ClassificationResult", "ClassificationScore", "ClassProbabilities", "ClassificationExplanation"}, {Text.Combine({newColumn, "ClassificationResult"}, "."), Text.Combine({newColumn, "ClassificationScore"}, "."), Text.Combine({newColumn, "ClassProbabilities"}, "."), Text.Combine({newColumn, "ClassificationExplanation"}, ".")}) else Table.ExpandRecordColumn(AddScoringColumn, newColumn, {"ClassificationResult", "ClassificationScore", "ClassProbabilities", "ClassificationExplanation"}, {Text.Combine({newColumn, "ClassificationResult"}, "."), Text.Combine({newColumn, "ClassificationScore"}, "."), Text.Combine({newColumn, "ClassProbabilities"}, "."), Text.Combine({newColumn, "ClassificationExplanation"}, ".")}),
  ReplacedErrors = Table.ReplaceErrorValues(ExpandResultColumns, {{Text.Combine({newColumn, "ClassificationResult"}, "."), null}, {Text.Combine({newColumn, "ClassificationScore"}, "."), null}, {Text.Combine({newColumn, "ClassProbabilities"}, "."), null}, {Text.Combine({newColumn, "ClassificationExplanation"}, "."), null}}),
  TransformTypes = Table.TransformColumnTypes(ReplacedErrors, {{Text.Combine({newColumn, "ClassificationResult"}, "."), type text}, {Text.Combine({newColumn, "ClassificationScore"}, "."), type text}, {Text.Combine({newColumn, "ClassProbabilities"}, "."), type text}, {Text.Combine({newColumn, "ClassificationExplanation"}, "."), type text}})
in
  TransformTypes
in
  ApplyScoringFunction;
shared HFC_PollingQuery = let
  Source = "2ed24f42-98d8-466c-ae5e-210722560ee2"
in
  Source;
shared #"HFC Testing Data" = let
  Source = #"HFC Training Data",
  DataflowPrecalculatedSource = PowerBI.Dataflows(),
  Workspace = DataflowPrecalculatedSource{[workspaceId = "fbe83004-d2da-4dc1-a5e2-0d30ab7e618d"]}[Data],
  Dataflow = Workspace{[dataflowId = "4f64cf66-2713-4e6d-a37c-68d02bc7a1cd"]}[Data],
  TestingPreview = Dataflow{[entity = "HFC Testing Data"]}[Data],
  #"Filtered rows" = Table.SelectRows(Source, each ([__IsTraining__] = false)),
  AddExplanationsIndex = Table.AddIndexColumn(#"Filtered rows", Text.Combine({"HFCOutput", "ExplanationIndex"}, "."), 1, 1),
  #"Invoked Scoring" = HFC.Score(AddExplanationsIndex, "HFCOutput"),
  #"Test Result" = if (Table.First(#"Invoked Scoring")[#"HFCOutput.ClassificationExplanation"]="Unavailable") = true then #"TestingPreview" else #"Invoked Scoring"
in
  #"Test Result";

Hi,

I think the issue is with the datasource in this case sql server rather than the data itself. Please details of the error.