Combining project date and product to correspond to the company

[mukhan311]

I have the dataset as above and want to row wise arrange the date, project, amount and product with respect to each company.
I tried pivting and unpivot but wasn't able to arrive at the conclusion.

Any help with this will be appreciated.

Thank you.

[mukhan311]

Hi all,
Thank you for the input and please find below the sample data below along with the expected output. 

Raw sample data:

Company name

Amount Total

Project

date

Product

Project2

product

date

amount

project3

product

amount3

date

Honda

16000

10001

11-Jan-23

headlamp

10004

rework

11-Feb-23

1200

10007

taillamp

1200

17-Feb-23

Yamaha

17000

10002

12-Jan

headlamp

10005

repair

12-Feb-23

1250

10008

fender

1400

19-Feb-23

Ducati

18000

10003

13-Jan-23

headlamp

10006

conversion

13-Feb-23

1300

10009

suspension

1500

13-Mar-23

Expected output:

company name	amount	Project	date	Product
Honda	16000	10001	11-Jan-23
	1200	10004	11-Feb-23	rework
	1200	10007	17-Feb-23	taillamp

[lbendlin]

You cannot have multiple columns with the same name. You are missing the amount column for the first group.

You will also want to unpivot the data to bring it into a usable format.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY7dCoIwFIBfJXatoM7f+4gIeoAQL066cKSbTK3X72xjTqKuvnH2ne2ra3KWooMDCUicR1GkiYg14/ACIkwonnsG3QDjpMfJZqVIxd5SPa1+Yner75wCuQAfvrYLJzdBTW4wQg9m7BMSY/9JyJyVmYQJuLL6LmFzSuSDiY4ZJ7U/VPuA49rCwvW49AHmFfo7gG5WjmyleDE1cynsio/wXoWc13liwnmZvaPhFZQJaT4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Company name " = _t, #"Amount Total" = _t, Project1 = _t, date1 = _t, Product1 = _t, amount1 = _t, Project2 = _t, Product2 = _t, date2 = _t, amount2 = _t, Project3 = _t, Product3 = _t, amount3 = _t, date3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Company name ", type text}, {"Amount Total", Int64.Type}, {"Project1", Int64.Type}, {"date1", type date}, {"Product1", type text}, {"amount1", Int64.Type}, {"Project2", Int64.Type}, {"Product2", type text}, {"date2", type date}, {"amount2", Int64.Type}, {"Project3", Int64.Type}, {"Product3", type text}, {"amount3", Int64.Type}, {"date3", type date}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Company name ", "Amount Total"}, "Attribute", "Value"),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Attribute.1", "Attribute.2"}),
    #"Pivoted Column" = Table.Pivot(#"Split Column by Character Transition", List.Distinct(#"Split Column by Character Transition"[Attribute.1]), "Attribute.1", "Value")
in
    #"Pivoted Column"

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[adudani]

hi @mukhan311 ,

try grouping by date and project. use the sum of amount and sort it as needed

if this doesn't work, kindly provide a sample input and output masking senstitive data in the form of a table/sharable excel/ g sheet etc.