Consolidate the rows

[LearnPowerBIAS]

Hi Friends,

I am using table visual to show the data, but the values are getting something like like, how to consoliadte all the rows and show a single row.

Company

Category

number

Active

ITDate

ITSales

ITAssets

ITLiabilities

MechDate

MechSales

MechAssets

MechLiabilites

CoreDate

Coresales

CoreAssets

CoreLiabilites

ABC LLC

B1

1001

Yes

ABC LLC

B1

1001

Yes

12/31/2021

2

1250000

10000

ABC LLC

B2

1001

Yes

1/1/2022

2

120000

1100000

ABC LLC

B3

1001

Yes

13000

50000

ABC LLC

B4

1001

Yes

6/30/2022

1

100000

145000

[Anonymous]

Hi @LearnPowerBIAS ,

Please click "Transform Data" in Desktop to enter the Power Query editor, then open "Advanced Editor" and copy the following code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVvDxcVbSUXIyBBKGBgYgKjK1GEgSRrE65JlgaKRvbKhvZGAEUmoEFjA1AAKIfiCNZrARPoMN9cFGGcGNgpkENsoAn4ONsZtraAzRZ4qhH49ZJuhmmekbG8AcZgjzGdhhJqZ4zY0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Company = _t, Category = _t, number = _t, Active = _t, ITDate = _t, ITSales = _t, ITAssets = _t, ITLiabilities = _t, MechDate = _t, MechSales = _t, MechAssets = _t, MechLiabilites = _t, CoreDate = _t, Coresales = _t, CoreAssets = _t, CoreLiabilites = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Company", type text}, {"Category", type text}, {"number", Int64.Type}, {"Active", type text}, {"ITDate", type date}, {"ITSales", Int64.Type}, {"ITAssets", Int64.Type}, {"ITLiabilities", Int64.Type}, {"MechDate", type date}, {"MechSales", Int64.Type}, {"MechAssets", Int64.Type}, {"MechLiabilites", Int64.Type}, {"CoreDate", type date}, {"Coresales", Int64.Type}, {"CoreAssets", Int64.Type}, {"CoreLiabilites", Int64.Type}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type"),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Transposed Table", {{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}}, "en-US"),{"Column1", "Column2", "Column3", "Column4", "Column5"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
    #"Transposed Table1" = Table.Transpose(#"Merged Columns"),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Transposed Table1", {{"Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1", type text}}),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type1", {"Column1"}),
    #"Split Column by Delimiter1" = Table.ExpandListColumn(Table.TransformColumns(#"Removed Duplicates", {{"Column3", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column3"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column3", Int64.Type}}),
    #"Removed Duplicates1" = Table.Distinct(#"Changed Type2", {"Column3"}),
    #"Split Column by Delimiter2" = Table.ExpandListColumn(Table.TransformColumns(#"Removed Duplicates1", {{"Column4", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column4"),
    #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Column4", type text}}),
    #"Removed Duplicates2" = Table.Distinct(#"Changed Type3", {"Column4"})
in
    #"Removed Duplicates2"

Then the result is as follows.

If you want to delete the first row, select "Remove Top rows".

Best Regards,

Neeko Tang

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

[vicky_]

They won't show all on the same row because the Category is different in each row. If that's not an important field, then you can remove it from the visual.

[LearnPowerBIAS]

Okay. how to delete the first row?  the whole row is empty apart from company.