Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

v-rzhou-msft

How to transform table in Power Query

Scenario:  

In many cases, the table model we imported cannot be visualized directly in the PowerBI Desktop because the attribute characteristics are added to the table record in the form of rows. We first need to convert the table model in the Power Query.

 

Initially imported table model is like below:

1.png

 

Expected result

2.png

 

The basic idea:

In PowerBI, the field characteristics of a certain attribute are displayed in the form of columns, and the data type of each row value added is the same.

 

Detailed steps

1.  Analyze the row value attributes in the initial table, and separate the values of the same type in Power Query

3.png

 

2.  Change the field to the correct data type:

 4.png

 

3.  Delete the sum value which corresponds to the grouping row in the attribute, because the column field can be automatically aggregated in PowerBI Desktop and you will see as below:

5.png

 

Attached: “Text.Contains”

6.png

 

Its function is Detects whether the text text contains the text substring. Returns true if the text is found. The above formula means to delete rows containing "TOTAL".

 

4.  Use "Add condition column" to create a new column to separate the attribute field

7.png

8.png

 

5.  Get the attribute grouping of the result we need in the first column, and use the “fill down” function to fill the row values under the same group. Finally delete the corresponding row in the original table and you will see as below:

9.png

 

Attached: “Table.FillUp”

10.png

 

Its function is to return a table from the table specified where the value of the next cell is propagated to the null-valued cells above in the columns specified. Fill blank rows with selected fields in the above formula.

 

6.  The same operation logic, find out the grouping of the second column field in the result graph, and use "Add condition column" to create a new column:

11.png

 

7.  Use the “fill up” function and delete the corresponding row in the original table and you will see as below:

12.png

13.png

 

8.  At this point, we have obtained the desired result basically. If we need to filter or calculate the “Sales shipment” field later, we can use the "Extract" function to filter out the numeric part of the value. Convert text type to number type and you will see as below:

 14.png15.png

 

Attached: “Table.TransformColumns:

16.png

 

Its function is to return a table from the input table by applying the transform operation to the column specified in the parameter transformOperations. If the column doesn't exist, an exception is thrown unless the optional parameter defaultTransformation specifies an alternative. Keep the last three values in each line of text in the above formula.

 

9.  At last, we successfully obtained a table model that satisfies the visualization operation and calculation in PowerBI Desktop. You will see as below:

17.png

 

Through the above steps, we can make a series of modifications to the imported irregular data model in Power Query, and finally build a data model that can be used for subsequent visualization operations or calculations on PowerBI Desktop. Hope this article helps everyone with similar questions here.

 

The code for related operations is provided here:

 

let

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fVA9C8IwEP0rR+YKySWnrsVJqEZoFykdOhQUWhXs/8crNfXQmIwv795XXStnNT8DK9j5wyk/nsGoTKkmq5XfF0Baw9AzYpi1oBt6oxjQ6j62PZRt3z2hvFwfQ3cbgXWZY+XlJDOfUkyP0nL4N4gV4JIZBYj0KZKysExZB4qv8gLCQnIfFyjzJ4r5MDof2/9GsekobhqEIr1MdFBMq5E0FMW+s28nx+YF", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Description = _t, Euro = _t]),

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Description", type text}, {"Euro", Int64.Type}}),

    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each not Text.Contains([Description], "TOTAL")),

    #"Added Conditional Column" = Table.AddColumn(#"Filtered Rows", "Customer", each if Text.Contains([Description], "COMPANY") then [Description] else null),

    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Customer"}),

    #"Filtered Rows1" = Table.SelectRows(#"Filled Down", each not Text.Contains([Description], "COMPANY")),

    #"Added Conditional Column1" = Table.AddColumn(#"Filtered Rows1", "Sales Shipment", each if Text.Contains([Description], "Sales Shipment") then [Description] else null),

    #"Filled Up" = Table.FillUp(#"Added Conditional Column1",{"Sales Shipment"}),

    #"Filtered Rows2" = Table.SelectRows(#"Filled Up", each not Text.Contains([Description], "Total Sales")),

    #"Extracted Last Characters" = Table.TransformColumns(#"Filtered Rows2", {{"Sales Shipment", each Text.End(_, 3), type text}}),

    #"Reordered Columns" = Table.ReorderColumns(#"Extracted Last Characters",{"Customer", "Sales Shipment", "Description", "Euro"})

in

#"Reordered Columns"

 

 

 

Author: Henry

Reviewer: Ula Huang, Kerry Wang