Forum Discussion

jdballard30's avatar
jdballard30
Helper II
6 years ago
Solved

Generate a Table Containing Metadata About Other Tables

I've brought several tables of a CRM system data into Power BI, and I'd like to use Power BI (if it makes sense) to try and generate some statistics about each column of each table. For example, say that I've brought in the account table with the following data:

AccountId

AccountName

Address1

abc123

Acme Corp

123 Main Street

xyz456

WidgetCo

 

mno225

Joe's Appliances

15503 US Highway 27

 

I'd like to generate a table that looks like this, based on the above table:

TableName

FieldName

MaxLength

BlankValues

Account

AccountId

6

0

Account

AccountName

16

0

Account

Address1

19

1

 

Is something like this possible? If so, I can't wrap my head around how to do it.

  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, jdballard30 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    Then you may create a blank query and input the following m codes in 'Advanced Editor'.

    let
    Source = Table.FromRecords(
       {
           [TableName="Account"]
       }
    ),
    #"Added Custom" = Table.AddColumn(Source, "AccountName", each Table.ColumnNames(Table)),
    #"Expanded AccountName" = Table.ExpandListColumn(#"Added Custom", "AccountName"),
    #"Added Custom1" = Table.AddColumn(#"Expanded AccountName", "MaxLength", each if [AccountName]="AccountId"
then 
List.Max(
    List.Transform(
    Table.Column(Table,"AccountId"),
    each Text.Length(_)
    )
)
else if [AccountName]="AccountName"
then
List.Max(
    List.Transform(
       Table.Column(Table,"AccountName"),
       each Text.Length(Text.From(_)) 
    )
)
else if [AccountName]="Address1"
then
List.Max(
    List.Transform(
       Table.Column(Table,"Address1"),
       each Text.Length(Text.From(_)) 
    )
)
else 
null
),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "BlankValues", each let 
tab1 = Table.SelectRows(Table,each [AccountId]=""),
tab2 = Table.SelectRows(Table,each [AccountName]=""),
tab3 = Table.SelectRows(Table,each [Address1]="")
in
if [AccountName]="AccountId"
then 
Table.RowCount(
    tab1
)
else if [AccountName]="AccountName"
then
Table.RowCount(
    tab2
)
else if [AccountName]="Address1"
then
Table.RowCount(
    tab3
)
else 
null)
in
    #"Added Custom2"

     

    Result:

     

    Best Regards

    Allan

     

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

2 Replies

  • Ajinkya369's avatar
    Ajinkya369
    Resolver III
    6 years ago

    Hi jdballard30 ,

     

    Yes it is possible using the Power query editor.All you have to do is go to query editor -> Create a new step by clicking on fx

    And then type this Function: Table.Schema(Source)

    Table.Schema(Source)

     

    For Has Blank value use a condtional column Go to Add column -> Condtional Column

    if account id equal = leave blank then output 1

    if account name equal = leave blank then output 1

    if address equal = leave blank then output 1

    else = 0

     

    If your problem is solved then accept this reply as a solution

     

    Thank you

    Ajinkya

    Master Data Analysis - 

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    Hi, jdballard30 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    Then you may create a blank query and input the following m codes in 'Advanced Editor'.

    let
    Source = Table.FromRecords(
       {
           [TableName="Account"]
       }
    ),
    #"Added Custom" = Table.AddColumn(Source, "AccountName", each Table.ColumnNames(Table)),
    #"Expanded AccountName" = Table.ExpandListColumn(#"Added Custom", "AccountName"),
    #"Added Custom1" = Table.AddColumn(#"Expanded AccountName", "MaxLength", each if [AccountName]="AccountId"
then 
List.Max(
    List.Transform(
    Table.Column(Table,"AccountId"),
    each Text.Length(_)
    )
)
else if [AccountName]="AccountName"
then
List.Max(
    List.Transform(
       Table.Column(Table,"AccountName"),
       each Text.Length(Text.From(_)) 
    )
)
else if [AccountName]="Address1"
then
List.Max(
    List.Transform(
       Table.Column(Table,"Address1"),
       each Text.Length(Text.From(_)) 
    )
)
else 
null
),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "BlankValues", each let 
tab1 = Table.SelectRows(Table,each [AccountId]=""),
tab2 = Table.SelectRows(Table,each [AccountName]=""),
tab3 = Table.SelectRows(Table,each [Address1]="")
in
if [AccountName]="AccountId"
then 
Table.RowCount(
    tab1
)
else if [AccountName]="AccountName"
then
Table.RowCount(
    tab2
)
else if [AccountName]="Address1"
then
Table.RowCount(
    tab3
)
else 
null)
in
    #"Added Custom2"

     

    Result:

     

    Best Regards

    Allan

     

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